Counif Between Date Ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey guys,

I'm creating a sheet to basically track downtime within a certain production
Cell, at the moment i have used mainly countif statements as well as
Sumproduct to tally up the minutes when 2 conditions are true. for example
the cell is BA and the operation is a setup, repair etc.

Now i need to narrow it down to downtime in a specific week, how would i
enter a sumproduct and counif to run between certain dates ?

Example of the sheet data would be ..

Date Cell Operation mins
01/10/2007 BA Setup 50
03/10/2007 Filters Repair 30

Any help would be greatly appreciated ...
 
Hi,

maybe:-

=SUMPRODUCT((A1:A3>E1)*(D1:D3))-(SUMPRODUCT((A1:A3>E2)*(D1:D3)))

With E1 & E2 being the 2 dates you want to sum between.

Mike
 
Or
=SUMPRODUCT((A1:A3>E1)*(A1:A3<E2)*(D1:D3))

You can add extra conditions as required, such as
=SUMPRODUCT((A1:A3>E1)*(A1:A3<E2)*(A1:A3="Filters")*(D1:D3))

Also, if you have ONLY conditions, you get the equivalent of COUNTIF:
=SUMPRODUCT((A1:A3>E1)*(A1:A3<E2)*(A1:A3="Filters"))

Stephen
 
Back
Top