Using SUMIF for a financial value AND a DATE

[spudsnruf]

Hi, I'm trying to use a SUMIF formula to add up 2 criteria.
1) financial value above £5 AND
2) before a date of 1/2/2008

Using a simple table example....

£10 2/3/2008
£5 12/1/2008
£8 10/12/2007
£12 1/1/2007

I would be looking for an formula that gave me £20.

I think its something to do with ...
=sumif(--

.... type equation, but I cant work out exactly what it is , or how I do the
BEFORE DATE OF 1/2/2008 bit.

Thank you very much

 

[David Biddulph]

=SUMPRODUCT((A2:A5>C$1)*(A2:A5)*(B2:B5<D$1))
if your data table is in columns A and B, and your £5 criterion in C1 and
the 1/2/2008 date criterion in D1.

 

[T. Valko]

Try one of these:

If there might be empty date cells:

D1 = 1/2/2008

=SUMPRODUCT(--(A1:A4>5),--(ISNUMBER(B1:B4)),--(B1:B4<D1),A1:A4)

If there won't be empty date cells:

=SUMPRODUCT(--(A1:A4>5),--(B1:B4<D1),A1:A4)

 

[Gary''s Student]

How about:

=SUMPRODUCT(--(B1:B4<DATE(2008,1,2))*(A1:A4>5)*(A1:A4))

 

[spudsnruf]

Hi, still wrestling with this one. I think I need to explain it more
thoroughly.


WORKSHEET 1
A B
1 Mike =SUMIF('Costs detail'!$D$2:$D$502,B29,'Costs detail'!$L$2:$L$502)

So in cell B1, I am currently looking in the 2nd Worksheet called Costs
detail, and looking for all entries for Mike (in cells D2 to D502) and
returning the amount earned (in cells L2 to 502)

But now what I need to do is change the formula in B1, so that it also looks
in to WORKSHEET 2 (the costs detail sheet) and returns only values for dates
(in col G) that are before the date of 31/1/2008, relating to Mike.

Hope this helps. Its not rocket science. But it may as well be sometimes.
Thank you

 

[Fred Smith]

When you have more than one criteria, you need to use Sumproduct rather than
Sumif. So you want:

=sumproduct(--('Costs detail'!$D$2:$D$502=B29),--('Costs
detail'!$G$2:$G$502<date(2008,1,31)),'Costs detail'!$L$2:$L$502)

Regards,
Fred

 

[Obonden]

Thanks a lot, Fred. I had the same problem as spudsnruf, and you solved it!

 

[Fred Smith]

Great. Thanks for the feedback.

Fred.

Thanks a lot, Fred. I had the same problem as spudsnruf, and you solved
it!

 

[spudsnruf]

Fred, worked perfectly. Brilliant and thanks so much.

 

[spudsnruf]

Thanks very much. In the end I went with the vesion that fred smith sent
after I'd explained it a little better. Thank you for your answer anyway.