Excel 2007 SUMIFS - why isn't this working?

Participant · 23 Dec 2009 at 10:40

Hi all

Please can you look at the below sample I've put toegther to try and teach myself this new formula, and tell me why it isn't chucking out 97? It's just giving me "0".

Many thanks.

Malakite · 23 Dec 2009 at 10:46

I cant view the picture due to being on a work machine but if you just type the formula I can have a look for you.

PMKeates · 23 Dec 2009 at 11:01

You're asking it to sum the rows where there is a "C" AND "Cap" (it's an and, not or), of which there are no rows, and therefore it's 0. If you want to sum either/or, do a traditional SUMIF for "C" + another SUMIF for "Cap".

Rich_L · 23 Dec 2009 at 11:02

It's because SUMIFS looks for the cells which match both criteria - there are no values which have both "C" and "Cap" in.

If you change "C" to "F" you will get 57 as a result.

Participant · 23 Dec 2009 at 11:13

Doh! Thanks all. Seems obvious now.

DSUM / pivots it is then.

FYI Malakite the formula was =SUMIFS(Q15:Q20,O15:O20,"C",P15

20,"Cap").