SUMIF help required

S

Suddes

Dear gods of Excel can you please help,
I have the following list, assume todays date is 25/11

Date Type Sq Ins
23/11 C 10
23/11 M 15
23/11 C 25
24/11 C 25
24/11 C 30
24/11 M 25
24/11 C 10
25/11 C 5
25/11 M 15
25/11 C 20

I have the following formula =SUMIF(B2:B11,"C",C2:C11)
This adds up all my ‘C’ another formula adds up my ‘M’
What I want to do is the same but only add up if date is today (ie 25/11)
and another formula which adds up for all of yesterday (24/11)
I don’t want to change the formula every time, so I don’t want to reference
today’s and yesterdays date in numbers. I hope this is making sense

How do I add this to my formula
 
J

JE McGimpsey

If all you want is yesterday and today, and your dates are actual dates,
not text:

Today:

=SUMPRODUCT(--(A1:A100=TODAY()),--(B1:B100="C"),C1:C100)

Yesterday:

=SUMPRODUCT(--(A1:A100=TODAY()-1),--(B1:B100="C"),C1:C100)


OTOH, if you want "C" and "M" values for all data, use a Pivot Table. If
you're not familiar with PTs, see Debra Dalgleish's introduction:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


For an explanation of the "--" in SUMPRODUCT, see

http://www.mcgimpsey.com/excel/doubleneg.html
 
S

Suddes

Perfect many thanks, excellent links too.

JE McGimpsey said:
If all you want is yesterday and today, and your dates are actual dates,
not text:

Today:

=SUMPRODUCT(--(A1:A100=TODAY()),--(B1:B100="C"),C1:C100)

Yesterday:

=SUMPRODUCT(--(A1:A100=TODAY()-1),--(B1:B100="C"),C1:C100)


OTOH, if you want "C" and "M" values for all data, use a Pivot Table. If
you're not familiar with PTs, see Debra Dalgleish's introduction:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


For an explanation of the "--" in SUMPRODUCT, see

http://www.mcgimpsey.com/excel/doubleneg.html
Click to expand...
 
Top