counting multiple columns

[Tom G]

Hi,

I'm using the following formula(s) to count how man instances of a
certain number I have in three different columns. The problem is that
it is working for most of the numbers but not for others. Only the
numbers 1-12 are in the cells.

EX.

=COUNTIF($N$5:$N$34:$P$5:$P$34:$R$5:$R$34,2)

=COUNTIF($N$5:$N$34:$P$5:$P$34:$R$5:$R$34,4)

Here are two examples. The top forumula is for counting the number of
cells in the three columns that contain the number 2. The bottom
formula does the same thing for the number 4. I get the right answer
for the "4" formula, but for the "2" formula I get an incorrect
answer.

I guess I must be doing something wrong.

Thanks,

Tom

 

[Bob Phillips]

It is a coincidence if any are correct as

$N$5:$N$34:$P$5:$P$34:$R$5:$R$34 is equivalent to

$N$5:$R$34

not

$N$5:$N$34 and $P$5:$P$34 and $R$5:$R$34

Try


=SUMPRODUCT(($N$5:$N$34=2)+($P$5:$P$34=2)+($R$5:$R$34=2))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Tom G]

It is a coincidence if any are correct as

$N$5:$N$34:$P$5:$P$34:$R$5:$R$34 is equivalent to

$N$5:$R$34

not

$N$5:$N$34 and $P$5:$P$34 and $R$5:$R$34

Try


=SUMPRODUCT(($N$5:$N$34=2)+($P$5:$P$34=2)+($R$5:$R$34=2))

Hi Bob, thanks for that.

Is there any way to pull down that formula for twelve different
numbers?

A B
1 4
2 7
3 8
4 2
5 0
6
7
8
9
10
11
12

What I'm doing is finding the number of 1s through 12s in the three
columns and putting them next to their corresponding number. In the
above example there are 4 1s and 7 2s and 0 5s etc.

Let's assume I put your formula in B1. So the formula would read:

=SUMPRODUCT(($N$5:$N$34=1)+($P$5:$P$34=1)+($R$5:$R$34=1))

Now I need to put the formula in B2 through B12. Is there an easy way
to do that, because I need to change the 1 to 2 and then a 3, 4 etc.
all the way down. I tried edit replace, but it didn't seem to work.

Thanks again for your help,

Tom

 

[Gord Dibben]

Tom

Try this................entered in B1:B12

=SUMPRODUCT(($N$5:$N$34=ROW())+($P$5:$P$34=ROW())+($R$5:$R$34=ROW()))

ROW() returns the Row number so will change from 1 to 2 to 3.........12 as
dragged down from B1


Gord Dibben MS Excel MVP

 

[Ragdyer]

Just to insure that you start with a "1", where header rows might be
involved to offset the starting number, suggest you might try either of
these:

=SUMPRODUCT(($N$5:$N$34=ROW(A1))+($P$5:$P$34=ROW(A1))+($R$5:$R$34=ROW(A1)))

=SUMPRODUCT(($N$5:$N$34=ROWS($1:1))+($P$5:$P$34=ROWS($1:1))+($R$5:$R$34=ROWS
($1:1)))

The second example is longer BUT more robust, as it will withstand inserting
and deleting rows.

 

[Tom G]

Just to insure that you start with a "1", where header rows might be
involved to offset the starting number, suggest you might try either of
these:

=SUMPRODUCT(($N$5:$N$34=ROW(A1))+($P$5:$P$34=ROW(A1))+($R$5:$R$34=ROW(A1)))

=SUMPRODUCT(($N$5:$N$34=ROWS($1:1))+($P$5:$P$34=ROWS($1:1))+($R$5:$R$34=ROWS
($1:1)))

The second example is longer BUT more robust, as it will withstand inserting
and deleting rows.

Hi guys, just an update. I really appreciate the advice.

I couldn't get this formula to work
=SUMPRODUCT(($N$5:$N$34=ROW())+($P$5:$P$34=ROW())+($R$5:$R$34=ROW()))

But I was able to get both of these formulas to work.

=SUMPRODUCT(($N$5:$N$34=ROW(A1))+($P$5:$P$34=ROW(A1))+($R$5:$R$34=ROW(A1)))

=SUMPRODUCT(($N$5:$N$34=ROWS($1:1))+($P$5:$P$34=ROWS($1:1))+($R$5:$R$34=ROWS
($1:1)))


The third formula wouldn't work until I copied and pasted it into
notepad and got it all on one line. For some reason that last part on
the second line ($1:1))) was causing a problem, but it worked fine
once I made it all one line.

I really like the idea of the third formula, where you can insert and
delete rows. That is pretty nifty, and I wish I had known that in the
past. I'm inserting and deleting rows all the time. Very nice.

One other thing. The way my spreadsheet is set up I'm not using the
formulas in B1 like I said in the example. I just said that for
clarity. I'm actually using it in N36, which is just underneath my
spreadsheet box--and they still work! So does that mean I can insert
the formula just about anywhere I want it, drag it down and it will do
the job? I'm assuming yes.

Great work guys, thanks a lot.

Tom

 

[Bob Phillips]

The problem you encountered is common here in the NGs, wrap-around. If you
had looked at the formula bar, you would have seen that the formula was
split over multiple lines. it is not the split per se that is the problem,
rather where it splits. You could just delete the splits and re-join in the
formula bar.

s to where you place it, yes you can, that was the whole purpose of RD's
amendment.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)