How can i make sumifs and countifs count mutiple criteria?

[tonywatsonhelp]

Hi Everyone,

I use sumifs and countifs a lot, one of the things i'd like to do is include a sumifs and a countifs that includes mutiple critieria, let me explain

So Column J is a persons name
and Column F is the areas he works in.

So If i want to see how James has done in Essex I would put
=Sumifs(B:B,J:J,"James",F:F,"Essex")

But how can i do this for both Essex and Sussex (for example)

please help if you can
Thanks
Tony

 

[Fluff]

How about

=sumproduct(Sumifs(B:B,J:J,"James",F:F,{"Essex","Sussex"})

 

[tonywatsonhelp]

Thanks Fluff,
just one problem totally my fault I assumed i could just change this but "Essex" and "Sussex" need to be cell references so i can change them so its actually A1 and C1
i tried
=sumproduct(Sumifs(B:B,J:J,"James",F:F,{A1,C1}) but it doesn't work, have i missed something?
sorry i should have said this before just assumed it would work the same?

Thanks

Tony

 

[Fluff]

You would be better of putting the the values in contiguous cells, otherwise you might get incorrect results. Are you happy to do that?

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(SUMIFS(B:B,J:J,"James",F:F,CHOOSE({1,2},A1,C1)))

M.

 

[tonywatsonhelp]

Thanks Fluff, I can put them in Cells A1:A2 sure

 

[Fluff]

In that case how about

=sumproduct(Sumifs(B:B,J:J,"James",F:F,A1:A2)

although it would be better to limit the ranges, rather than using whole columns.

 

[tonywatsonhelp]

Hi Fluff,
This is brilliant, thank you
Tony

 

[Fluff]

Glad we could help & thanks for the feedback.