PDA

View Full Version : [SOLVED] Filtered List - loop through only counting "visible" rows based on Criteria



AA_20069
11-08-2016, 04:21 AM
If all,,
hoping for some help....

Scenario:

One spreadsheet that has columns A:Z with filter a on O (Filter = "Orange")
Columns C,D,E also contain various "states" will go with Yes,NO,Maybe,Never
Column F also has various "states" will go with Okay,Sort,Leave,Move,Mix
The last data row would be row 1024 (this is a fixed row)

Once the filer has been applied to O ("Orange") then what I am looking to achieve is the following:

If C,D,E to Row 1024 = NO AND F = Okay count all of these and in Cell D1040 out the total
If C,D,E to Row 1024 = NO AND F = Sort count all of these and in Cell D1041 out the total
If C,D,E to Row 1024 = NO AND F = Leave count all of these and in Cell D1042 out the total
If C,D,E to Row 1024 = NO AND F = Move count all of these and in Cell D1043 out the total
If C,D,E to Row 1024 = NO AND F = Mix count all of these and in Cell D1044 out the total

Any help would be greatly appreciated

Paul_Hossler
11-08-2016, 07:02 AM
I'd use the COUNTIFS() WS formula

Small sample of data, and the results are not where you wanted them but you can adjust

17532

snb
11-08-2016, 08:00 AM
=sumproduct((C1:C1024&D1:D1024&E1:E1024&F1:F1024&O1:O1024="NONONOOkayOrange")*1)
=sumproduct((C1:C1024&D1:D1024&E1:E1024&F1:F1024&O1:O1024="NONONOSortOrange")*1)
=sumproduct((C1:C1024&D1:D1024&E1:E1024&F1:F1024&O1:O1024="NONONOLeave")*1)
=sumproduct((C1:C1024&D1:D1024&E1:E1024&F1:F1024&O1:O1024="NONONOMoveOrange")*1)
=sumproduct((C1:C1024&D1:D1024&E1:E1024&F1:F1024&O1:O1024="NONONOMixOrange")*1)

AA_20069
11-08-2016, 09:23 AM
:bow::bow::beerchug: Thanks a lot guys - headache has been resolved.....