Hello!
I’m trying to find a formula for an Excel 2010 workbook that will identify the number of unique ids in one column that meet a condition in a second column then returns a count of 1 in the cell for the first instance then 0 for each recurring instance. Ultimately the count columns will be used in a pivot table to summarize the results.
This sample chart shows a company code in column G and the negative number of days an invoice is due in column M.
In cell AC4 I’m using the formula =IF(SUMPRODUCT(($A$4:$A41=A41)*($G$4:$G41=G41))>1,0,1)which is copied down and works because the list only contains invoices <-1 days past due.
The formula in column AD needs to return 1 at the first invoice <-30 days then 0 for any recurring instance that meets that criteria. The formula in AE needs to return 1 at the first invoice <-90 days past due then 0 for any recurring instance that meets that criteria.
The values have been keyed in AD and AE just to demonstrate the desired result.
Thank you in advance for your help/advice!
<tbody>
</tbody>
I’m trying to find a formula for an Excel 2010 workbook that will identify the number of unique ids in one column that meet a condition in a second column then returns a count of 1 in the cell for the first instance then 0 for each recurring instance. Ultimately the count columns will be used in a pivot table to summarize the results.
This sample chart shows a company code in column G and the negative number of days an invoice is due in column M.
In cell AC4 I’m using the formula =IF(SUMPRODUCT(($A$4:$A41=A41)*($G$4:$G41=G41))>1,0,1)which is copied down and works because the list only contains invoices <-1 days past due.
The formula in column AD needs to return 1 at the first invoice <-30 days then 0 for any recurring instance that meets that criteria. The formula in AE needs to return 1 at the first invoice <-90 days past due then 0 for any recurring instance that meets that criteria.
The values have been keyed in AD and AE just to demonstrate the desired result.
Thank you in advance for your help/advice!
A | G | M | AC | AD | AE | |
3 | State | Comp Code | Days Past Due | # of Comp 1+ Days PD | # of Comp 30+ Days PD | # of Comp 90+ Days PD |
4 | CA | 545 | -7 | 1 | 0 | 0 |
5 | CA | 545 | -15 | 0 | 0 | 0 |
6 | AZ | 842 | -35 | 1 | 1 | 0 |
7 | AZ | 842 | -95 | 0 | 0 | 1 |
8 | AZ | 842 | -105 | 0 | 0 | 0 |
9 | IL | 974 | -2 | 1 | 0 | 0 |
10 | IL | 974 | -31 | 0 | 1 | 0 |
11 | IL | 974 | -60 | 0 | 0 | 0 |
12 | IL | 974 | -100 | 0 | 0 | 1 |
<tbody>
</tbody>