SUMIFS usage

Hi All

I have the following Data ( file attached)

I am trying to find sum of counts where Region = 'EMEA' and Region = 'NAMER'.

I am trying to use SUMIFS. It's not working. So where I am going wrong?

Any other way of doing the same?

Please Login or Register  to view this content.

is correct.

Please Login or Register  to view this content.

Are both searching for conditions where the A6:A10 range has both EMEA and NAMER as their exact entries, which of course is wrong.

Well, I would just recommend using the D6 formula because it's fast and obvious (easy to read when you come back next month!)

Bit if you want other options....
=SUMPRODUCT(B6:B10, --((A6:A10 = "EMEA") + (A6:A10 = "NAMER") > 0))
Use addition to create an OR conditional in SUMPRODUCT.

Array constructions...
{=SUM(IF(A6:A10="EMEA", B6:B10)) + SUM(IF(A6:A10="NAMER", B6:B10))}
{=SUM(IF(A6:A10="EMEA", B6:B10, IF(A6:A10="NAMER", B6:B10, 0)))}

confirm with CTRL+SHIFT+ENTER, not just ENTER.

Hi Special-K

Thanks for your quick response. Now if I use your solution I won't get Sum of Counts where both the criteria is matching.

In this case the answer should be 6. 1+1+2+2.

Hi ben_hensel

Thanks for such a wonderful explanation.

I really appreciate your efforts.