COUNTIF problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Francis Hookham - 25 Sep 2006 19:11 GMT

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

Reply to this Message

Herbert Seidenberg - 26 Sep 2006 20:58 GMT

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

Reply to this Message

Francis Hookham - 27 Sep 2006 10:33 GMT

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!

> Here is a way using Pivot Table.
> Arrange your data like this:
[quoted text clipped - 42 lines]
>
> To sort Value, click on Value > Field Settings > Advanced > Ascending

Reply to this Message

Herbert Seidenberg - 28 Sep 2006 04:52 GMT

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))

Reply to this Message