SUMIF AND

  • Thread starter Thread starter Gerrym
  • Start date Start date
G

Gerrym

I want to total figures if a code is found and this works
fine with SUMIF. However I want to include an AND so,
if the code is found AND another condition is met. Can I
include an AND in SUMIF.
 
Hi
you can't include an 'AND' within SUMIF but you can use
SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100="test1"),--(B1:B100="test2"))
 
No. You need SUMPRODUCT,

=SUMPRODUCT(--(A1:A1000="A"),--(B1:B1000="B"),C1,C1000)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Maybe I need to explain further.
in Sheet1, In COL A I have budget code, Col B date COL c
Amount

In Sheet2, in COL A I have budget code in COL B MONTH(for
amounts)

So in sheet2 COL B I want to sum all amounts in sheet1 COL
C whete Month in the date in COL B in Sheet1 = 7


SHEET1

A B C
Budg CDE DATE AMOUNT
133456 20/07/04 200.00
152455 15/07/04 150.00
458752 14/06/04 100.00
133456 17/07/04 50.00


Results in Sheet2 would be:

SHEET2
A B
BUDG CDE JULY
133456 250.00
152455 150.00


Hope this helps clarify
 
Hi,

Assuming that Sheet2!B1, which contains "JULY", is a label (text) and
not a date value...

Sheet2!B2, copied down:

=SUMPRODUCT((Sheet1!$A$2:$A$100=Sheet2!$A2)*(TEXT(Sheet1!$B$2:$B$100,"mmm
m")=Sheet2!B$1)*(Sheet1!$C$2:$C$100))

Hope this helps!
 
Back
Top