multiple operations

**seshultz** · 07-09-2012, 09:23 PM

Hello,
I am interested in entering data for several months on one worksheet and displaying the totals and percentages from each month, as well as the cumulative data, on another worksheet.

The formula I am using for one of these data ranges is =COUNTIF(Data!I3:I39,"Yes")/COUNTA(Data!I3:I39) where "Data" is the name of my worksheet with the data entry and "yes" is the response for the percentage of entries answered yes. I've added a column for date entered and formatted in full month, date, and year, so the full month name can be another identifier to separate the individual data.

I've tried =COUNTIF(Data! E3:E39, "*June*")(Data!I3:I39,"Yes")/COUNTA(Data!I3:I39) and a few variations with the parentheses in different places, but am not having luck.

The conditions I want met are:
Calculate percentage of entries that were answered yes for all
Calculate percentage of entries that were answered yes for June (and every month)

Thanks in advance!!!

Sarah

**jeffreybrown** · 07-09-2012, 09:28 PM

Hello Sarah,

How about...

Formula:

=COUNTIFS(Data!$E$3:$E$39,"*June*",Data!$I$3:$I$39,"Yes")/COUNTA(Data!$I$3:$I$39)

**seshultz** · 07-09-2012, 09:45 PM

Thank you so much for the quick response Jeff!

I'm still not getting exactly what I'm expecting though...I might not be explaining it well.

I think I've attached it...I am working on cell D4 on "Results" tab with the explanation below. Thank you very much if you care to take a look!

Sarah

**jeffreybrown** · 07-09-2012, 09:56 PM

I'm not entirely sure what you wanted to do with BP and Cognitive, but maybe this gives you an idea.

If you need to add Cognitive and BP into the Averageifs, just add another pair.

IMO, this easiest thing to do is add a helper column to the Data tab (Column F).

Let me know if you have follow-up questions.

**seshultz** · 07-09-2012, 10:06 PM

Thank you so much! This looks great. I will work on it some more...thank you!

Sarah

**jeffreybrown** · 07-09-2012, 10:12 PM

You are most welcome. Glad you have something to work with and thanks for the feedback

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**vlady** · 07-09-2012, 10:18 PM

and also maybe you can use sumproduct... in result D4

=SUMPRODUCT(--(MONTH(Data!$E$3:$E$16)=6),--(Data!$J$3:$J$16="yes"))/SUMPRODUCT(--(Data!$J$3:$J$16<>""))