sumif with 2 conditions ?? can this be done??

[WTG]

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

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Aladin Akyurek]

[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.