Sum based off two colum criteria

[AllenCraig]

OK so I have a unique problem.

I have a sheet that I am doing some vlookups with and I need to sum some values based of what is has in both column a and b for instance I have

ADEBDATX 02/07/14 D0002242 PHY 21.92 No
ADEBDATX 02/14/14 D0002323 PHY 21.92 No
ADEBDATX 02/21/14 D0002405 STO 25 No
ADEBDATX 02/21/14 D0002405 STO 50 No
ADRIGRTX 02/07/14 D0002243 ADF 420.58 No
ADRIGRTX 02/07/14 D0002243 ADF 365.22 No
ADRIGRTX 02/07/14 D0002243 ADF 350.34 No

I need to for each row that has ADEBDATX so sum PHY, STO and for each row with ADRIGRTX to sum ADF (There would also be rows with STO FSC etc etc)

So I need to if column a = ADEBDATX and column D = PHY sum sum E wher d= PHY; then sum e where d = sto... Really I need to be able to use somet

(That may not have been clear so here is another attempt)

I want to search the sheet for any row with ADEBDATX and sum column E for all those rows that also have ADF in column D.

[hemesh]

assuming your data is arranged in A1 to F10 then in H1 enter ADEDATX in I1 enter ADF then in J1 try below
=SUMIFS($E$1:$E$7,$A$1:$A$7,H1,$D$1:$D$7,I1)

Incase you want to search two criteria in same range i.e. PHY Or STO
then use as below
=SUMPRODUCT(SUMIFS($E$1:$E$7,$A$1:$A$7,H1,$D$1:$D$7,{"PHY";"STO"}))

syntax for sumifs is as below
=sumifs(range to sum, criteria range1, criteria to search in criteria range1.... and so on for other criteria)

[AllenCraig]

OK I am using =SUMIFS(Sheet1!E1:E3439,B4:B500,B4,Sheet1!D1:D3439,"BOB") in the atatched sheet but its returning #value! it looks like the references are right but the logical test is off...

O-O Pay Summary Feb 2014.xlsx

Thanks

[AlKey]

Deleted post

[hemesh]

Hello Craig what you are trying to achieve!

[hemesh]

try using
=SUMIFS(Sheet1!E1:E3439,Sheet1!A1:A3439,B4,Sheet1!D1:D3439,"BOB")

[AllenCraig]

So what if I wanted to grab multiple items on the second criteria ie =SUMIFS(Sheet1!E1:E3439,Sheet1!A1:A3439,B4,Sheet1!D1:D3439,"BOB";"STO";"STP") i tired that no go.

[Tony Valko]

Try it like this...

=SUM(SUMIFS(Sheet1!E1:E3439,Sheet1!A1:A3439,B4,Sheet1!D1:D3439,{"BOB","STO","STP"}))

[AllenCraig]

P.S. The equation appears to be working perfectly. and my last question using =SUMPRODUCT(SUMIFS(Sheet1!E1:E3439,Sheet1!A1:A3439,B4,Sheet1!D1:D3439,{"STO";"BOB"})) worked

[oeldere]

with an pivot table.

see the attached file.