How can I count IF, then, else statements that have values?

[Guest]

I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

 

[Scoops]

I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

 

[PCLIVE]

Here's something you might play around with:

=A1&B1&"=" & COUNTIF(E$2:E$7,E2)

This formula could be copied down as needed. However, you'll end up with
duplicates. So if you know all of the names ahead of time, then you can
work that into the equation.

Good luck.

 

[Guest]

I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

 

[Scoops]

I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

Hi Jenny

A Pivot Table should be able to do the work you want.

Regards

Steve

 

[Guest]

Why not do a simple table with names in rows and "animals" in columns and put
SUMPRODUCT statement in cell referencing apporpriate row/column cells and
copy accross down
Dog Cat ... etc
Jane =Sumproduct(....=Jane, .....=Cat)
Jim
Jeff

 

[Guest]

because part of the data is being imported somewhere else.

 

[Guest]

a pivot table won't work

Hi Jenny

A Pivot Table should be able to do the work you want.

Regards

Steve

 

[Scoops]

a pivot table won't work

Hi Jenny

It seems you haven't given us all the relevant information

A pivot table works fine for me based on the data layout you described
and Toppers' SUMPRODUCT solution is eminently workable too (I was
looking at that before offering pivot table as a built-in solution).

Regards

Steve