| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I count IF, then, else statements that have values?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
Jenny wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
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. "Scoops" wrote in message oups.com... Jenny wrote: 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 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
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" wrote: Jenny wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
Jenny wrote: 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" wrote: Jenny wrote: 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 Hi Jenny A Pivot Table should be able to do the work you want. Regards Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
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 "Jenny" wrote: 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" wrote: Jenny wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
because part of the data is being imported somewhere else.
"Toppers" wrote: 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 "Jenny" wrote: 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" wrote: Jenny wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
a pivot table won't work
"Scoops" wrote: Jenny wrote: 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" wrote: Jenny wrote: 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 Hi Jenny A Pivot Table should be able to do the work you want. Regards Steve |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I count IF, then, else statements that have values?
Jenny wrote: a pivot table won't work "Scoops" wrote: Jenny wrote: 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" wrote: Jenny wrote: 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 Hi Jenny A Pivot Table should be able to do the work you want. Regards Steve 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count consecutive dates only | Excel Discussion (Misc queries) | |||
| Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
| retirning count values from calcualted fields | Excel Discussion (Misc queries) | |||
| Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
| Count Unique Values | Excel Worksheet Functions | |||
Linear Mode
