COUNTIF with two conditions

Hi,

I've got a spreadsheet with 8 columns (A,B,C,D,E,F,G,H)
Column A has an identifier (1,2,3,4)
Column B has the Start Date
Column C has the $ value
Column D is for < 30days
Column E is for < 60days
Column F is for < 90days
Column G is for >=120 days
Cell H1 has todays date ( =today() )

I'm getting stuck with the conditional counting..
What I want to do is populate cells D>G with a count from Column A for each Identifier (1,2,3,4).

Basically Columns D>G should contain:
Count of identifier 'A' where todays date is <30 days from date in B,
Count of identifier 'A' where todays date is <60 days from date in B,
Count of identifier 'A' todays date is <90 days from date in B,
Count of identifier 'A' todays date is >=120 days from date in B

Count of identifier 'B' where todays date is <30 days from date in B,
Count of identifier 'B' where todays date is <60 days from date in B,
Count of identifier 'B' where todays date is <90 days from date in B,
Count of identifier 'B' where todays date is >=120 days from date in B

etc, etc. for all identifiers.

Once I get this running, I will then also SUM the total for each of the results in columns D>G for each identifier. Eg. There are 10 instances of 'A' that have been running for <30days for a total of $10,000 value.


Any help you could give for the above would be greatly appreciated..

Originally Posted by firebladejr

Hi,

I've got a spreadsheet with 8 columns (A,B,C,D,E,F,G,H)
Column A has an identifier (1,2,3,4)
Column B has the Start Date
Column C has the $ value
Column D is for < 30days
Column E is for < 60days
Column F is for < 90days
Column G is for >=120 days
Cell H1 has todays date ( =today() )

I'm getting stuck with the conditional counting..
What I want to do is populate cells D>G with a count from Column A for each Identifier (1,2,3,4).

Basically Columns D>G should contain:
Count of identifier 'A' where todays date is <30 days from date in B,
Count of identifier 'A' where todays date is <60 days from date in B,
Count of identifier 'A' todays date is <90 days from date in B,
Count of identifier 'A' todays date is >=120 days from date in B

Count of identifier 'B' where todays date is <30 days from date in B,
Count of identifier 'B' where todays date is <60 days from date in B,
Count of identifier 'B' where todays date is <90 days from date in B,
Count of identifier 'B' where todays date is >=120 days from date in B

etc, etc. for all identifiers.

Once I get this running, I will then also SUM the total for each of the results in columns D>G for each identifier. Eg. There are 10 instances of 'A' that have been running for <30days for a total of $10,000 value.


Any help you could give for the above would be greatly appreciated..

Hi,

I'm attaching a sample workbook which i think contains teh essence of what you're after. There appears to be a gap in the ageing periods you mention above. What about days >90 and <120. I'm assuming this is a typo and have assumed col G is for values older than 90 days.

HTH