Nested IF / SUMIF statements

[Ian]

I'm trying to determine a sum via a number of SUMIF's, and am failing badly !

To explain, I have 4 columns with data in - Names, Town, Month, Sales. I
need to show the total Sales number where the Names and Town and Month are
the same.

eg
=sumif(a:a="Smith",sumif(b:b="London",sumif(c:c="Jan",(d:d))))

 

[Pete_UK]

You can only use SUMIF with one criterion - if you have more then you can
use SUMPRODUCT, like this:

=SUMPRODUCT((A1:A100="Smith")*(B1:B100="London")*(C1:C100="Jan"),D1100)

All the ranges must be the same length, but you can't use full-column ranges
(unless you have XL2007). If you have proper dates in column C then you will
have to use something like:

(TEXT(C1:C100,"mmm")="Jan")

and you might also have to take account of the year, depending on how your
file is set up.

Hope this helps.

Pete

 

[Stephen C]

You can use the sumif in an array, use the formula below but hold in
ctrl+shift when you press enter.

=SUM(IF(A34:A39="Smith",IF(B34:B39="London",IF(C34:C39="Jan",(D3439)))))