KlayontKress
Board Regular
- Joined
- Jan 20, 2016
- Messages
- 67
- Office Version
- 2016
- Platform
- Windows
I'm looking for a way to count unique values using criteria in 3 columns (I need to find the unique values comparing all three columns) as well as only counting values between a specific date range. I have a series of formulas to count the number of items in a single column that are between a date range and contain a specific text and I tried to adapt another formula, listed below, to include this date range counting criteria but it doesn't work. When I tried running an error checker, it appears to count the first instance and then stops counting.
The formula I found online to count unique items across criteria in multiple columns is:
I have another formula that counts items across a date range that is also compare multiple criteria:
I am attempting to use the red text above to modify the formula listed above that to count unique values across 4 columns.
<tbody>
</tbody>
The formula I've tried is:
The formula returns a #Div/0! error
Does anyone know how to modify the formula to meet the criteria I need?
Thanks in advance for any help you can provide.
The formula I found online to count unique items across criteria in multiple columns is:
Code:
=SUMPRODUCT((1/COUNTIFS(U9:U21,U9:U21,V9:V21,V9:V21,W9:W21,W9:W21)))
I have another formula that counts items across a date range that is also compare multiple criteria:
Code:
=COUNTIFS('Breakdown Data (Don''t Filter)'!$L$2:$L$300000,"*00000*",[COLOR=#ff0000]'Breakdown Data (Don''t Filter)'!$S$2:$S$300000,">="&$B$1,'Breakdown Data (Don''t Filter)'!$S$2:$S$300000,"<="&$B$2[/COLOR],'Breakdown Data (Don''t Filter)'!$F$2:$F$300000,$A8)
I am attempting to use the red text above to modify the formula listed above that to count unique values across 4 columns.
A | B | C | D | E | F | G | H | I | |
1 | Name | Number | Letter | Code | Date | Start Date | 1/10/18 | ||
2 | Bob | 1 | A | ZZ1 | 1/10/18 | End Date | 1/15/18 | ||
3 | Bob | 1 | B | ZZ1 | 1/10/18 | ||||
4 | Bob | 2 | A | ZZ1 | 1/10/18 | # of unique items | (should be 5 entries, between the date range and across all 4 columns. They are the red text items to the left) | ||
5 | Bob | 2 | A | ZZ1 | 1/11/18 | ||||
6 | John | 1 | A | ZZ1 | 1/8/18 | ||||
7 | John | 2 | A | ZZ1 | 1/13/18 | ||||
8 | Susan | 1 | A | ZZ1 | 1/8/18 | ||||
9 | Susan | 1 | B | ZZ1 | 1/9/18 | ||||
10 | Joe | 1 | A | ZZ1 | 1/13/18 | ||||
11 | Joe | 1 | A | ZZ1 | 1/11/18 | ||||
12 | Joe | 2 | A | ZZ1 | 1/16/18 | ||||
13 | |||||||||
14 | |||||||||
15 | |||||||||
16 | |||||||||
17 | |||||||||
18 | |||||||||
19 |
<tbody>
</tbody>
The formula I've tried is:
Code:
=SUMPRODUCT((1/COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12,C2:C12,C2:C12,D2:D12,D2:D12,E2:E12,">="&$I$1,E2:E12,"<="&$I$2)
The formula returns a #Div/0! error
Does anyone know how to modify the formula to meet the criteria I need?
Thanks in advance for any help you can provide.