Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counif Between Date Ranges
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 ... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counif Between Date Ranges
Hi,
maybe:- =SUMPRODUCT((A1:A3E1)*(D1:D3))-(SUMPRODUCT((A1:A3E2)*(D1:D3))) With E1 & E2 being the 2 dates you want to sum between. Mike "Karma2400" wrote: 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 ... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counif Between Date Ranges
Or
=SUMPRODUCT((A1:A3E1)*(A1:A3<E2)*(D1:D3)) You can add extra conditions as required, such as =SUMPRODUCT((A1:A3E1)*(A1:A3<E2)*(A1:A3="Filters" )*(D1:D3)) Also, if you have ONLY conditions, you get the equivalent of COUNTIF: =SUMPRODUCT((A1:A3E1)*(A1:A3<E2)*(A1:A3="Filters" )) Stephen "Mike H" wrote in message ... Hi, maybe:- =SUMPRODUCT((A1:A3E1)*(D1:D3))-(SUMPRODUCT((A1:A3E2)*(D1:D3))) With E1 & E2 being the 2 dates you want to sum between. Mike "Karma2400" wrote: 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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counif multiple condition | Excel Worksheet Functions | |||
counif greater or less than | Excel Worksheet Functions | |||
Counif with different cells, not a range | Excel Discussion (Misc queries) | |||
Counif | Excel Discussion (Misc queries) | |||
counif but not all the cells | Excel Worksheet Functions |