COUNTIF problem

[Francis Hookham]

COUNTIF problem - I hope this makes sense!



A10 heads a column of box sizes:
40
50
..
..
220
230



B10:H10 contain:

Ar Et . . Sl Sy



On another sheet are two columns named 'AllLGs' and 'BoxHeights':
AllLGs BoxHeights
Ar 0001 100
Ar 0002 110
Ar 0004 110
..
..
Sy 2076 120
Sy 0278 50
Sy 0279 90



I am trying to count the number of boxes of each size under each heading but
this depends on both the box size below A10 and the LEFT(AllGGs,2) equalling
the column headers in B10:H10



So I was hoping the following would do it but it wont:

=COUNTIF(AND(BoxHeights,RC1),(left(AllLGs,2)=R10C))



I trust I have explained the problem well enough - maybe COUNTIF will not
accept two criteria using AND - or am I just not thinking clearly?

Francis Hookham

 

[Herbert Seidenberg]

Here is a way using Pivot Table.
Arrange your data like this:

Ar 190
Ar 50
Ar 180
..... ....
Ar 60
Et 140
Et 210
..... ....
Et 90
..... ....
Si 160
Si 230
..... ....
Si 160
Sy 160
Sy 180
..... ....
Sy 210

Data > Pivot Table > Multiple consolidation ranges
Range: Select the above data. Include a blank row on top
Layout: Drag the Row button out of ROW and Column out of COLUMN
and drag Value into ROW and Row into COLUMN.
Change Sum of Value to Count of Value.
Options: Uncheck Grand Totals and Autoformat.
The Pivot Table might look like this:

Value Ar Et Si Sy
40 1 1
50 1 1
60 3
80 3 2
90 2 2 2
140 2 2
160 2 1
180 3 2
190 2 1
200 1
210 1 2
220 1 2
230 2

To sort Value, click on Value > Field Settings > Advanced > Ascending

 

[Francis Hookham]

Thanks - I'll try it - I have avoided pivot tables to date - I suppose I
have not really understood how/where to use them - now you have shown me I
shall have a go!

 

[Herbert Seidenberg]

As a backup, here is a formula solution.
Assume the block of data below is located at R1C1.
Ags and BxHt refer to the 2 columns in my previous post.
I used R1C1 Ref style. Mixing Ref styles spells trouble.

Ar Et Si Sy
40 0 1 1 0
50 1 1 0 0
60 3 0 0 0
70 0 0 0 0
.... ... ... ... ...
230 0 0 2 0

=SUMPRODUCT((Ags=R1C)*(BxHt=RC1))