Want to sum by using four criterias two being numbers and two being
alphabetic all on a seperate worksheet from the one gathering the information
within a range of cells. IF A2:A4 = "111" and IF B2:B4 = "AA" and IF C2:C4 =
"999" and IF D2:D4 = "BB" THEN SUM E2:E4.

This seems complicated to me and I cannot resolve it. If anyone can help
please let me know... Thanks...

=SUMPRODUCT((A2:A4="111")*(B2:B4="AA")*(C2:C4="999 ")*(D2:D4="BB")*(E2:E4)

--

Vasant

"Sean" wrote in message
...
Want to sum by using four criterias two being numbers and two being
alphabetic all on a seperate worksheet from the one gathering the
information
within a range of cells. IF A2:A4 = "111" and IF B2:B4 = "AA" and IF
C2:C4 =
"999" and IF D2:D4 = "BB" THEN SUM E2:E4.

This seems complicated to me and I cannot resolve it. If anyone can help
please let me know... Thanks...

Hi Sean

One line...
=SUMPRODUCT((Sheet1!A2:A4=111)*(Sheet1!B2:B4="AA") *(Sheet1!C2:C4=999)*(Sheet1!D2:D4="BB")*Sheet1!E2: E4)

You could also put the 4 criteria into cells and reference those cells in
the formula instead....
=SUMPRODUCT((Sheet1!A2:A4=H1)*(Sheet1!B2:B4=H2)*(S heet1!C2:C4=H3)*(Sheet1!D2:D4=H4)*Sheet1!E2:E4)

--

XL2003
Regards

William



"Sean" wrote in message
...
Want to sum by using four criterias two being numbers and two being
alphabetic all on a seperate worksheet from the one gathering the
information
within a range of cells. IF A2:A4 = "111" and IF B2:B4 = "AA" and IF
C2:C4 =
"999" and IF D2:D4 = "BB" THEN SUM E2:E4.

This seems complicated to me and I cannot resolve it. If anyone can help
please let me know... Thanks...