Multiple Sum IF ... is it possible ??

[Guest]

I have a range a cells as below:

A B C D E
Sue 1 N Jan-07 20,000
Jo 3 N Feb-07 10,000
Peter 4 Y Jan-07 30,000
Sue 5 Y Jan-07 20,000
Sue 7 Y Jan-07 10,000

I want to calculate total value of E when A=Sue, B=>4, C=Y and
D=Jan-07...therefore the answer being 30,000

PLEASE HELP !!!!!! Its so frustrating when you are trying to learn......

 

[Guest]

You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A50="Sue),--(B2:B50>=4),--(C2:C50="Y"),--(D250="Jan-07"),E2:E50)

however that will probably fail unless the Jan-07 etc is text. You need to
check what the dates are, I assume that you mean that it is January 2007 but
Excel cannot deal with real dates like that, it needs a day and on a US
Excel if you type in Jan-07 in a cell and today's with current year 2006 the
date it will in fact be January 7 2006.
If you truly want 2007 you need to type in 1/1/2007 and use a custom format
of mmm-yy, then you can change the formula to

=SUMPRODUCT(--(A2:A50="Sue),--(B2:B50>=4),--(C2:C50="Y"),--(D250=Date(2007,1,1)),E2:E50)

also it is better to remove the hard coded entries like "Sue", 4 etc and
replace them with cells where you type in the criteria
Same with the date

=SUMPRODUCT(--(A2:A50=F2),--(B2:B50>=G2),--(C2:C50=H2),--(D250=I2),E2:E50)

for example, that way you don't need to alter the formula, just what you
type in those criteria cells

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

I tried all sorts but never got the right answer !!!


Can you help please ????

 

[Guest]

Add column F as a hidden column and put the following formula in each row of
column F:

=IF(AND(A1="Sue", B1>=4, C1="Y"), "Y", "N")

Then do a normal SUMIF formula in another cell, as follows:

=SUMIF(F1:F5, "Y", E1:E5)

Make sure that there are no trailing spaces after your names or "Y"/"N" or
the column F formulas won't work. I realize this is not exactly what you
were after, however, it should be a convenient enough work around.

 

[Guest]

=SUMPRODUCT(--(A1:A100="Sue"),--(B1:B100>=4),--(C1:C100="Y"),--(MONTH(D1100)=1),--(YEAR(D1100)=2007),E1:E100)

 

[Guest]

i am also trying and confused
but try this...with your data on rows 1:5
assuming that column D contain real sensible dates (not a text) formatted in
"mmm-yy"
somewhere at the bottom of your table, say on row 6, summing criteria
A6: sue
B6 : 4
C6: Y
D6 : 1/1/2007 : (this represent the whole month of Jan-07)

formula to sum only

E6
=SUM((A1:A5=A6)*(B1:B5>=B6)*(C1:C5=C6)*(MONTH(D15)=MONTH(D6))*(YEAR(D15)=YEAR(D6))*E1:E5)

 

[Guest]

dont forget the ctrl-shift-enter on E6 - while in edit mode .

 

[Guest]

Thank you all for your help....great knowledge you all have...

Mark