What's wrong with this formula? SUMIFS / COUNTIFS

[trikky]

I'm looking for some help trying to figure out what's wrong with my formula and why it doesn't generate expected results.

The table below shows my current results to help with understanding my question and challenge. My ultimate goal is to have each month broken down individually.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]261[/TD]
[TD]Topic[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]262[/TD]
[TD]Happy[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]263[/TD]
[TD]Sad[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[TD]89%[/TD]
[/TR]
</tbody>[/TABLE]


Row 263 shows is a basic SUMIFS that currently gets the average score for Yes/No answers. Right now it's an overall score for year to date and it seems to be working fine. That formula is

=IFERROR(SUMIFS(DATA!$E$1:$E$15668,DATA!$H$1:$H$15668,Report!$B9,DATA!$F$1:$F$15668,Report!G$8)/COUNTIFS(DATA!$H$1:$H$15668,Report!$B9,DATA!$F$1:$F$15668,Report!G$8),"N/A")

However, what I want to do is have the score broken down by month, so on my data source I have added column 'Q', and in Q I have calculated the month name, as text in MMM, based on a date/time result elsewhere in the row. That formula is =TEXT(<cell reference>,"MMM") and the for November is as expected, simply "Nov". Fine so far.

However, when I add an extra criteria to my SUMIFS to filter out by month, it's not working. Looking back at my sample table above, in row 262, every month shows as N/A, including November, which - had it gone as I wanted, should have a score visible. The goal is to look at Column Q then if that is the same as the month name (row 261 on the sample table above) the cell in row 262 will show that average. The current formula, with my added criteria, is:

=IFERROR(SUMIFS(DATA!$E$1:$E$15668,DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$2:$Q$15668,N$261)/COUNTIFS(DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$2:$Q$15668,N$261),"N/A")


For clarification, the intended result would be something like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]261[/TD]
[TD]Topic[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]262[/TD]
[TD]Happy[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]89%[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]263[/TD]
[TD]Sad[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]89%[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]


There's obviously something missing here, but I can't figure it out.

I thank you all in advance for any assistance you can provide.

 

[NiMip]

EDITED to reflect additional comment below.

Hmm I honestly have no idea... hard to say without having a play around with the spreadsheet. But, try using AVERAGEIFS instead of doing the average calculation yourself. i.e. AVERAGEIFS(DATA!$E$1:$E$15668,DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$1:$Q$15668,N$261)

 

[NiMip]

Oh wait! I think you actually have an error in your formula! You have a mismatch in the dimensions of your ranges. All other ranges in your equation start at row 1, and your new addition you've started at row 2.

I still recommend using AVERAGEIFS as above, but if you want to keep your formula, try:
=IFERROR(SUMIFS(DATA!$E$1:$E$15668,DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$1:$Q$15668,N$261)/COUNTIFS(DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$1:$Q$15668,N$261),"N/A")

 

[trikky]

Oh wait! I think you actually have an error in your formula! You have a mismatch in the dimensions of your ranges. All other ranges in your equation start at row 1, and your new addition you've started at row 2.

I still recommend using AVERAGEIFS as above, but if you want to keep your formula, try:
=IFERROR(SUMIFS(DATA!$E$1:$E$15668,DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$1:$Q$15668,N$261)/COUNTIFS(DATA!$O$1:$O$15668,Report!$E$7,DATA!$F$1:$F$15668,$G$8,DATA!$Q$1:$Q$15668,N$261),"N/A")

Hi, thanks. I've both fixed that little error and uses the AVERAGEIFS but it still doesn't seem to be working.

Thanks for your help.

 

[NiMip]

Hmmm ok. Are you able to step through the formula using "Evaluate Formula" on the Formula tab and see where it's falling over?

 

[barry houdini]

What's in N261? - I see "Nov" in your screenshot but that has to be a text value, not a formatted date