One of three COUNTIFS criteria not working I am using Excel 2010. I am using the

Question

One of three COUNTIFS criteria not working

I am using Excel 2010. I am using the folowing formula in column I to get a percentage: =(SUMIFS($H$3:$H$5000,$A$3:$A$5000,A3,$D$3:$D$5000,D3)/COUNTIFS($A$3:$A$5000,A3,$D$3:$D$5000,D3))

Column A contains city names, column D contains an "Item" (3 different options). Column H is a list of 1s and blanks resulting from another formula. The formula is calulating correctly for two of the three options in D. Any time the third option appears in D, the formula ignores that criteria and gives me a percentage for everything matching Column A with a 1 in column H.

The three entries in D are "0 - 99uV/m", "100 - 199uV/m", and ">= 200uV/m". It is the last one that is not calcualting correctly. Since that is the only one that starts with a function symbol, I considered that might be the issue. Hopefully not since I cannot alter the source data.

Any suggestions welcome.

Explanation / Answer

You can apply SUMPRODUCT() to the SUMIFs() portion as well or use the second method of forcing the equals into the SUMIFS.

SUMPRODUCT(($H$3:$H$5000)*($A$3:$A$5000=A3)*($D$3:$D$5000=D3))

... or,

SUMIFS($H$3:$H$5000,$A$3:$A$5000,A3,$D$3:$D$5000,"="&D3)

The same change in syntax could be applied to the comparison in column A if there is a chance of the problem surfacing there.

SUMIFS($H$3:$H$5000,$A$3:$A$5000,"="&A3,$D$3:$D$5000,"="&D3)

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.