Hi, I'm trying to figure out a formula that would give me the unique number of values in column A based on multiple criteria:
-A date range (greater than one day, less than one day) (Column B)
-A certain value in column C
-A certain value in column D
I've searched through many topics but haven't been able to quite find the answer I want. Any help is much appreciated!
Thanks!
Upload a sample work book please, it'll help us understand better.
Thanks!
I'm assuming that "values in column A" means numbers?
Try something like this.
Use cells to hold the criteria.
F2 = start date
G2 = end date
H2 = column C criteria
I2 = column D criteria
Then, this array formula**:
=SUM(IF(FREQUENCY(IF(B2:B15>=F2,IF(B2:B15<=G2,IF(C2:C15=H2,IF(D2:D15=I2,A2:A15)))),A2:A15),1))
Adjust the ranges to suit.
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Values in column A would be text
This version will work on both text and numbers.
Array entered**:
=SUM(IF(FREQUENCY(IF(B2:B15>=F2,IF(B2:B15<=G2,IF(C2:C15=H2,IF(D2:D15=I2,MATCH(A2:A15,A2:A15,0))))),ROW(A2:A15)-ROW(A2)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
pookyman 
Register To Reply
Bookmarks