Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumif with 2 conditions ?? can this be done??
I have a worksheet as follows.
a b c d e 1 abc xyz 456 2 def poer 789 3 werq iug 123 4 kljh jklh 486 I need a sumif " if column a=def and b= poer then sum column e" I tried the following formula, but it didn't work :( =SUMIF(D_TICK!$a$1:$e$1000,('PIT & QUARRY'!F22)&('PIT & QUARRY'!H18),D_TICK!$e$1:1000) any ideas?? Wally |
#2
|
|||
|
|||
=SUMPRODUCT(--(A2:A100=""def"),--(B2:B100="poer"),E2:E100)
it cannot work on whole columds, only ranges in a column. -- HTH RP (remove nothere from the email address if mailing direct) "WTG" wrote in message ... I have a worksheet as follows. a b c d e 1 abc xyz 456 2 def poer 789 3 werq iug 123 4 kljh jklh 486 I need a sumif " if column a=def and b= poer then sum column e" I tried the following formula, but it didn't work :( =SUMIF(D_TICK!$a$1:$e$1000,('PIT & QUARRY'!F22)&('PIT & QUARRY'!H18),D_TICK!$e$1:1000) any ideas?? Wally |
#3
|
|||
|
|||
[1]
F1 on D_TICK, copied down: =A1&"#"&B1 Then invoke: =SUMIF(D_TICK!$F$1:$F$1000,'PIT & QUARRY'!F22&"#"&'PIT & QUARRY'!H18,D_TICK!$E$1:$E$1000) [2] =SUMPRODUCT((D_TICK!$A$1:$A$1000='PIT & QUARRY'!F22)+0,(D_TICK!$B$1:$B$1000='PIT & QUARRY'!H18)+0,D_TICK!$E$1:$E$1000) WTG wrote: I have a worksheet as follows. a b c d e 1 abc xyz 456 2 def poer 789 3 werq iug 123 4 kljh jklh 486 I need a sumif " if column a=def and b= poer then sum column e" I tried the following formula, but it didn't work :( =SUMIF(D_TICK!$a$1:$e$1000,('PIT & QUARRY'!F22)&('PIT & QUARRY'!H18),D_TICK!$e$1:1000) any ideas?? Wally -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with two conditions | Excel Discussion (Misc queries) | |||
sumif with two conditions | Excel Worksheet Functions | |||
SumIF w/ two conditions (not the same as other posts!... I think) | Excel Discussion (Misc queries) | |||
SUMIF with two conditions ? | Excel Discussion (Misc queries) | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |