Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
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...... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
You are frustrated. Why? What formula did you try to learn this? you can post
it here, don't be shy to learn. "Mark Allen" wrote: 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...... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
=SUMPRODUCT(--(A2:A50="Sue),--(B2:B50=4),--(C2:C50="Y"),--(D2:D50="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"),--(D2:D50=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),--(D2:D50=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) "Mark Allen" wrote in message ... 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...... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
I tried all sorts but never got the right answer !!!
Can you help please ???? "driller" wrote: You are frustrated. Why? What formula did you try to learn this? you can post it here, don't be shy to learn. "Mark Allen" wrote: 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...... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
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. "Mark Allen" wrote: 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...... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
=SUMPRODUCT(--(A1:A100="Sue"),--(B1:B100=4),--(C1:C100="Y"),--(MONTH(D1:D100)=1),--(YEAR(D1:D100)=2007),E1:E100)
"Mark Allen" wrote: 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...... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
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(D1:D 5)=MONTH(D6))*(YEAR(D1:D5)=YEAR(D6))*E1:E5) "Mark Allen" wrote: I tried all sorts but never got the right answer !!! Can you help please ???? "driller" wrote: You are frustrated. Why? What formula did you try to learn this? you can post it here, don't be shy to learn. "Mark Allen" wrote: 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...... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
dont forget the ctrl-shift-enter on E6 - while in edit mode .
"driller" wrote: 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(D1:D 5)=MONTH(D6))*(YEAR(D1:D5)=YEAR(D6))*E1:E5) "Mark Allen" wrote: I tried all sorts but never got the right answer !!! Can you help please ???? "driller" wrote: You are frustrated. Why? What formula did you try to learn this? you can post it here, don't be shy to learn. "Mark Allen" wrote: 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...... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sum IF ... is it possible ??
Thank you all for your help....great knowledge you all have...
Mark "Teethless mama" wrote: =SUMPRODUCT(--(A1:A100="Sue"),--(B1:B100=4),--(C1:C100="Y"),--(MONTH(D1:D100)=1),--(YEAR(D1:D100)=2007),E1:E100) "Mark Allen" wrote: 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...... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
work with multiple workbooks on separate monitor for 2003 edition | Excel Worksheet Functions | |||
Number of unique attributes that multiple people have | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions |