Nested IF / SUMIF statements
 

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="J an",(d:d))))

--
Many thanks

Ian

Nested IF / SUMIF statements
 

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"),D1:D100)

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

"Ian" wrote in message
...
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="J an",(d:d))))

--
Many thanks

Ian

Nested IF / SUMIF statements
 

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",(D34:D39)))))


"Ian" wrote:

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="J an",(d:d))))

--
Many thanks

Ian