Sumproduct search between Dates

[Robert Christie]

Hi

I have two sheets in a XP workbook.
Sheet1 contains three columns.
Dates Code Price.

Sheet 2, column A contains Codes starting on row 3.

I like to be able to enter two dates in sheet2 and have a
formula in column B reference those dates and the
adjacent code to search sheet1 between those dates and
return a total Price. (like a 2 criteria sumif)

I found a 2 criteria sumproduct formula using a single
date, but trying to modify it to using two dates has
stumped me.

Dates are formatted dd mm yy
Codes are Vehicle, Utilities, Household, Dinning etc.

TIA

Bob C,

 

[Frank Kabel]

Hi
if you enter both dates in A1 and A2 and A3 contains the code try the
following in B3
=SUMPRODUCT(--('sheet1'!$A$1:$A$100>=A1),--('sheet1'!$A$1:$A$100<=A2),-
-('sheet1'!$B$1:$B$100=B3),'sheet1'!$C$1:$C$100)

 

[Robert Christie]

Hi Frank

Your formula works perfectly.
Thankyou very much.

Regards Bob C.

 

[Robert Christie]

Hi Frank
Can your formula be modified to count the instances of
the code between the dates. i.e.
So B3 could read $560.00 and D3 would show 4 (the times
the code appears).

TIA
Regards Bob C

 

[Frank Kabel]

Hi
just remove the last parameter. Try:
=SUMPRODUCT(--('sheet1'!$A$1:$A$100>=A1),--('sheet1'!$A$1:$A$100<=A2),-
-('sheet1'!$B$1:$B$100=B3))

 

[Robert Christie]

Thankyou once again Frank

Bob C.

-----Original Message-----
Hi
just remove the last parameter. Try:
=SUMPRODUCT(--('sheet1'!$A$1:$A$100>=A1),--('sheet1'! $A$1:$A$100<=A2),-
-('sheet1'!$B$1:$B$100=B3))

--
Regards
Frank Kabel
Frankfurt, Germany


the code try
the

.