How do I count the number of items that match a particular value

[Bilalmemon]

Need Help

Data

Alpha 1
Beta 1
Gama 1
Beta 2
Gama 3
Gama 4
Alpha 1
Beta 2
Gama 1
Beta 4
Gama 4
Beta 3
Beta 4
Gama 1
Gama 2
Alpha 1
Beta 1



Report

1 2 3 4
Alpha 3 ? ? ?
Beta ? ? ? ?
Gama ? ? ? ?


Formullae required How "3" Comes In Alpha 1

123.jpg

[davsth]

removed as per rules

[Richard Buttrey]

Hi,

You could use a COUNTIFS function

e.g. assuming data is in A1:B17 and your table of results is in C1:G4

Please Login or Register  to view this content.

but personally I'd avoid a function altogether and use a Pivot table which for something like this is far more elegeant, efficient and flexible.

Incidentally please note we gerenarlly prefer you to upload a workbook rather than pictures.

[Shareez Saleem]

Assume that your table is in the range A1:B21 and you need counts in B24 to F26- use the formula COUNTIFS($A$1:$A$21,$A24,$B$1:$B$21,B$23) and copy and paste across horizontally and vertically.

[Raghav09]

I agree with Richard. you would want to avoid the formula to count instead use the pivot

[AliGW]

Pivot tables aren't for everyone: I hate them with a passion.

[sandy666]

Pivot tables aren't for everyone: I hate them with a passion.

If you don't like PT you can do that with PowerQuery but not with Ex2007

[Raghav09]

what i would to ease the formula is use the CONCATENATE(A1,B1) in C1 which gives me unique value in Column C.

Then i would count the unique one's

to count Alpha1 = COUNTIF(C1:C21,$C$1)

if you do not want to pick the criteria from C, we can list the total entries like Alpha1,Alpha2,Alpha3 etc., in different column to avoid any confusions.

Raghav