how do I count rows with text values in several columns.

[scmopar]

I need your help.

I have to group my answer by Platform and Size.

Column 1 has several platform names ie. "bob". It contains duplicates.

Column 2 contains text values such as small, meduim, large.

My answer needs to be

Bob Small = 1
Bob Medium = 20
Bob Large = 36.

I can use cell text for the platform names and the sizes.

This result will be used in a further financial calculation.

I have tried Sumproduct and CountIf and am lost.

Bob

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--($A$1:$A$100="Bob"),--($B$1:$B$100="Small"))

Better still
Enter Small, Medium and Large in cells F1:H1
Enter Bob etc. in cells E2 downward
in cell F2
=SUMPRODUCT(--($A$1:$A$100=$E2),--($B$1:$B$100=F$1))
Copy across and down as required

Or, create a Pivot Table
Assuming A1 contains Platform and B1 contains Size
Place cursor in A1
Data>Pivot Table>Finish
On the PT skeleton that occurs on the newly inserted sheet
Drag Platform to the Row area
Drag Size to the Column area
Drag Platform again, to the Data area where it will become Count of
Platform.

 

[scmopar]

Thanks a lot Roger, it worked.

Why the -- in the formula?

 

[Roger Govier]

Hi

Each cell in the range
$A$1:$A$100="Bob"
will return either True or False. Similarly for the range in B:B and Size.

The double unary minus is one way of coercing False to 0 and 1 to True, so
the resulting values can be used by Sumproduct

The formula could have been written as
=SUMPRODUCT(($A$1:$A$100="Bob")*($B$1:$B$100="Small"))
where multiplying the results would have achieved the same coercion.

False*False = 0*0 =0
False*True = 0*1 =0
True*False = 1*0 =0
True*True = 1*1 =1

Sumproduct adds all these results giving a count of when both cases are True

 

[scmopar]

Thanks, I also tried teh Pivot table. After I got used to it, it was nice.
Hard to understand.. But easier to recreate if the input data format
changes.