Using SUMIF for a financial value AND a DATE
 

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

Using SUMIF for a financial value AND a DATE
 

=SUMPRODUCT((A2:A5C$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.
--
David Biddulph

"spudsnruf" wrote in message
...
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

Using SUMIF for a financial value AND a DATE
 

Try one of these:

If there might be empty date cells:

D1 = 1/2/2008

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

If there won't be empty date cells:

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


--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
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

Using SUMIF for a financial value AND a DATE
 

How about:

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

--
Gary''s Student - gsnu200769


"spudsnruf" wrote:

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

Using SUMIF for a financial value AND a DATE
 

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




"Gary''s Student" wrote:

How about:

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

--
Gary''s Student - gsnu200769


"spudsnruf" wrote:

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

Using SUMIF for a financial value AND a DATE
 

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

"spudsnruf" wrote in message
...
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




"Gary''s Student" wrote:

How about:

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

--
Gary''s Student - gsnu200769


"spudsnruf" wrote:

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

Using SUMIF for a financial value AND a DATE
 

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


"Fred Smith" skrev i melding
...
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

Using SUMIF for a financial value AND a DATE
 

Great. Thanks for the feedback.

Fred.

"Obonden" <kjeblo(æt)frisurf.no wrote in message
...
Thanks a lot, Fred. I had the same problem as spudsnruf, and you solved
it!


"Fred Smith" skrev i melding
...
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

Using SUMIF for a financial value AND a DATE
 

Fred, worked perfectly. Brilliant and thanks so much.

"Fred Smith" wrote:

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

"spudsnruf" wrote in message
...
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




"Gary''s Student" wrote:

How about:

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

--
Gary''s Student - gsnu200769


"spudsnruf" wrote:

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

Using SUMIF for a financial value AND a DATE
 

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.

"T. Valko" wrote:

Try one of these:

If there might be empty date cells:

D1 = 1/2/2008

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

If there won't be empty date cells:

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


--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
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