Countif with Dynamic date ranges.
I have a worksheet *(sheeta) with a list of activities with "Open" and
"Closed" dates. In worksheet B, I have A2:A50 I have A B 1 =TODAY()-7 =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) 2 =TODAY()-14 ={number of occurances between A2 and A1} 3 =TODAY()-21 ={number of occurances between A3 and A2} 4 etc.... In Colum B1, I do a COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) In B2:B50, I want to do =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A2 AND "<" &'sheetb'!A1) THe idea is to found out, by week (each day the start date will change to TODAY()-7.) Each day. Any ideas? KSL |
Countif with Dynamic date ranges.
B2: =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A2)-SUM($B$1:$B1)
and copy down -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leonhardtk" wrote in message ... I have a worksheet *(sheeta) with a list of activities with "Open" and "Closed" dates. In worksheet B, I have A2:A50 I have A B 1 =TODAY()-7 =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) 2 =TODAY()-14 ={number of occurances between A2 and A1} 3 =TODAY()-21 ={number of occurances between A3 and A2} 4 etc.... In Colum B1, I do a COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) In B2:B50, I want to do =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A2 AND "<" &'sheetb'!A1) THe idea is to found out, by week (each day the start date will change to TODAY()-7.) Each day. Any ideas? KSL |
Countif with Dynamic date ranges.
Try:
=COUNTIF(sheeta!A1:A50,"=" &sheetb!A2)- COUNTIF(sheeta!A1:A50,"=" &sheetb!A1) "Leonhardtk" wrote: I have a worksheet *(sheeta) with a list of activities with "Open" and "Closed" dates. In worksheet B, I have A2:A50 I have A B 1 =TODAY()-7 =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) 2 =TODAY()-14 ={number of occurances between A2 and A1} 3 =TODAY()-21 ={number of occurances between A3 and A2} 4 etc.... In Colum B1, I do a COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A1) In B2:B50, I want to do =COUNTIF('sheeta'!A1:A50,"=" &'sheetb'!A2 AND "<" &'sheetb'!A1) THe idea is to found out, by week (each day the start date will change to TODAY()-7.) Each day. Any ideas? KSL |
All times are GMT +1. The time now is 03:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com