Looking for counts from an array

**Johnmitch93** · 01-27-2012, 04:03 PM

Evening all,

I have tried to use the countifs function to find the totals of 1s a 4s in the table.
No luck yet.

Table attached.

Cheers,
John.

**stanleydgromjr** · 01-27-2012, 04:27 PM

Johnmitch93,

The formula in cell B27:
=SUMPRODUCT(($A$2:$A$25="NORTH")*($B$2:$F$25=4))

returns:
4

The formula in cell B28:
=SUMPRODUCT(($A$2:$A$25="NORTH")*($B$2:$F$25=1))

returns:
10

**Johnmitch93** · 01-27-2012, 05:13 PM

Stanley,

That's good stuff, mate. Ta very much.

Is there an explaination why the countifs is no good? I was sure I had done it that way before.
Maybe my memory...

John.

**Haseeb A** · 01-27-2012, 05:42 PM

Hello John, All the ranges must be in same number of rows & columns, if you have multiple conditions.

Excel help says,

Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other

Or, you can use COUNTIFS with OFFSET. Note OFFSET is a volatile function

=SUMPRODUCT(COUNTIFS(A2:A25,"NORTH",OFFSET(B2:B25,0,{0,1,2,3,4}),4))

This is basically doing,

=COUNTIFS(A2:A25,"NORTH",B2:B25,4)+COUNTIFS(A2:A25,"NORTH",C2:C25,4)+...COUNTIFS(A2:A25,"NORTH",E2:E 25,4)

**Johnmitch93** · 01-27-2012, 06:03 PM

Well, that works too. Thanks Haseeb.

I am still puzzled why the countis do not work - I just checked and I do have one that works.

JOhn.

**daddylonglegs** · 01-27-2012, 06:18 PM

As Haseeb says, all ranges must be the same size. This, in fact, applies to all "IF" and "IFS" functions like SUMIF, COUNTIFS, AVERAGEIFS etc......because there's always a "one-to-one" relationship between the cells, e.g. in COUNTIFS(A2:B10,"x","C2:D10,"y") you are counting instances of "paired" cells where the conditions are met, e.g. A4 is paired with C4, B9 with D9 etc. Given that setup it doesn't make sense to allow the ranges to be different sizes.....

Note that SUMIF (and AVERAGEIF) allows you to "apparently" set ranges to different sizes, e.g.

=SUMIF(A:C,"x",D:D)

....but, in fact, the sum range always becomes the same size (and shape) as the criteria range so with that formula if you have "x"s in columns B and C then the corresponding cells in columns E and F are summed......even though those columns aren't included in the formula.