Sumproduct search between Dates

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

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,
 
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)
 
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
 
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))
 
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

.
Click to expand...
 
Back
Top