Steve Barbee
asked on
Excel - complex multi cell count
Looking for a formula to provide a count of occurring items within excel.
Here is an example sheet layout:
Column A Column B Column C
Connectivity Support Undetermined
Functionality No Action Request
Functionality Support Malfunction
Connectivity Support Malfunction
Connectivity Support Undetermined
Functionality Support Malfunction
(this continues for about 9500 rows)
Here are the results I'm trying to get: (answer can be in any column past "C")
Column XX Column XX Column XX Column XX (this is the formula)
Connectivity Support Undetermined 2
Connectivity Support Undetermined 1
Functionality Support Malfunction 2
Connectivity Support Malfunction 1
These count numbers match the above scenario, but they will each be in the thousands once the formula is executed.
That's it. Seemingly simple, but I do not have the time to work this out (work pressure? -ha!)
Here is an example sheet layout:
Column A Column B Column C
Connectivity Support Undetermined
Functionality No Action Request
Functionality Support Malfunction
Connectivity Support Malfunction
Connectivity Support Undetermined
Functionality Support Malfunction
(this continues for about 9500 rows)
Here are the results I'm trying to get: (answer can be in any column past "C")
Column XX Column XX Column XX Column XX (this is the formula)
Connectivity Support Undetermined 2
Connectivity Support Undetermined 1
Functionality Support Malfunction 2
Connectivity Support Malfunction 1
These count numbers match the above scenario, but they will each be in the thousands once the formula is executed.
That's it. Seemingly simple, but I do not have the time to work this out (work pressure? -ha!)
Earlier versions of excel then 2007 do not have COUNTIFS...
In which case use SUMPRODUCT:
=SUMPRODUCT(N(A:A=F1),N(B: B=G1),N(C: C=H1))
Though this will work far better without the full column refs:
e.g =SUMPRODUCT(N($A$1:$A$999= F1),N($B$1 :$B$999=G1 ),N($C$1:$ C$999=H1))
In which case use SUMPRODUCT:
=SUMPRODUCT(N(A:A=F1),N(B:
Though this will work far better without the full column refs:
e.g =SUMPRODUCT(N($A$1:$A$999=
Using the COUNTIFS function relies on you knowing the various combinations of the criteria.
How about a Pivot Table with a count for data values. This would then add the various combinations as they occur.
Thanks
Rob H
How about a Pivot Table with a count for data values. This would then add the various combinations as they occur.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All were good usable answers, but this is the one that worked best. Thanks for your help.
=COUNTIFS(A:A,F1,B:B,G1,C:
With the search values in F1,G1,H1 put the formula in I1
Example.xlsx