Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
I'm trying to get a count of how many times a value appears in either of 2
columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
I'm assuming you mean that Bonds could appear in either column. If it
appears in either column then count it. If it appaears in *both* columns count it a single appearance. =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds")) -- Biff Microsoft Excel MVP "king60611" wrote in message ... I'm trying to get a count of how many times a value appears in either of 2 columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
Hmmm...I thought that would work. However, it returned a #REF error (and I
know all of the cells in these ranges exist). But, I decided to play with taking out the $ to see if that did anything (I'm never really sure what they mean) and Excel corrected my formula to the following: =SUMPRODUCT(--'Master List'!D3:D3000="ATL")*('Master List'!H3:H3000="Bonds")+('Master List'!D3:D3000="Bonds") However, that is again returning a #VALUE error. Any other ideas? "T. Valko" wrote: I'm assuming you mean that Bonds could appear in either column. If it appears in either column then count it. If it appaears in *both* columns count it a single appearance. =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds")) -- Biff Microsoft Excel MVP "king60611" wrote in message ... I'm trying to get a count of how many times a value appears in either of 2 columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
The $ signs make the cell references absolute. That means the cell
references won't change if/when you copy the formula to another location. The $ signs will not cause a problem. A #REF! error means a reference in the formula is not valid or there may be #REF! errors already present in one of the referenced ranges. Since there's nothing wrong with the cell references that leaves either the sheet name or there are already #REF! errors in a range as a cause of the problem. Are you sure the sheet name is correct? Does the sheet exist? Try it like this: =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),SIGN(('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds"))) -- Biff Microsoft Excel MVP "king60611" wrote in message ... Hmmm...I thought that would work. However, it returned a #REF error (and I know all of the cells in these ranges exist). But, I decided to play with taking out the $ to see if that did anything (I'm never really sure what they mean) and Excel corrected my formula to the following: =SUMPRODUCT(--'Master List'!D3:D3000="ATL")*('Master List'!H3:H3000="Bonds")+('Master List'!D3:D3000="Bonds") However, that is again returning a #VALUE error. Any other ideas? "T. Valko" wrote: I'm assuming you mean that Bonds could appear in either column. If it appears in either column then count it. If it appaears in *both* columns count it a single appearance. =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds")) -- Biff Microsoft Excel MVP "king60611" wrote in message ... I'm trying to get a count of how many times a value appears in either of 2 columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
Alas, no luck. I'm still getting the #REF! error with that. I know that the
sheet exists, as does the range. I'm analyzing the same fields in other formulas, including conditional sums, etc. However, I've figured out a work around. By taking out the and/or clause, I've done 2 separate SUMPRODUCTS for each circumstance in hidden columns and will sum each of those for the final product. Thanks for your help. "T. Valko" wrote: The $ signs make the cell references absolute. That means the cell references won't change if/when you copy the formula to another location. The $ signs will not cause a problem. A #REF! error means a reference in the formula is not valid or there may be #REF! errors already present in one of the referenced ranges. Since there's nothing wrong with the cell references that leaves either the sheet name or there are already #REF! errors in a range as a cause of the problem. Are you sure the sheet name is correct? Does the sheet exist? Try it like this: =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),SIGN(('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds"))) -- Biff Microsoft Excel MVP "king60611" wrote in message ... Hmmm...I thought that would work. However, it returned a #REF error (and I know all of the cells in these ranges exist). But, I decided to play with taking out the $ to see if that did anything (I'm never really sure what they mean) and Excel corrected my formula to the following: =SUMPRODUCT(--'Master List'!D3:D3000="ATL")*('Master List'!H3:H3000="Bonds")+('Master List'!D3:D3000="Bonds") However, that is again returning a #VALUE error. Any other ideas? "T. Valko" wrote: I'm assuming you mean that Bonds could appear in either column. If it appears in either column then count it. If it appaears in *both* columns count it a single appearance. =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds")) -- Biff Microsoft Excel MVP "king60611" wrote in message ... I'm trying to get a count of how many times a value appears in either of 2 columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count w/ multiple variables & text values
If the sheet exists then there's no reason that I can see that will cause a
#REF! error. I would need to see the problem first-hand to figure what's going on. If you have something working then that's good! Thanks for the feedback! -- Biff Microsoft Excel MVP "king60611" wrote in message ... Alas, no luck. I'm still getting the #REF! error with that. I know that the sheet exists, as does the range. I'm analyzing the same fields in other formulas, including conditional sums, etc. However, I've figured out a work around. By taking out the and/or clause, I've done 2 separate SUMPRODUCTS for each circumstance in hidden columns and will sum each of those for the final product. Thanks for your help. "T. Valko" wrote: The $ signs make the cell references absolute. That means the cell references won't change if/when you copy the formula to another location. The $ signs will not cause a problem. A #REF! error means a reference in the formula is not valid or there may be #REF! errors already present in one of the referenced ranges. Since there's nothing wrong with the cell references that leaves either the sheet name or there are already #REF! errors in a range as a cause of the problem. Are you sure the sheet name is correct? Does the sheet exist? Try it like this: =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),SIGN(('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds"))) -- Biff Microsoft Excel MVP "king60611" wrote in message ... Hmmm...I thought that would work. However, it returned a #REF error (and I know all of the cells in these ranges exist). But, I decided to play with taking out the $ to see if that did anything (I'm never really sure what they mean) and Excel corrected my formula to the following: =SUMPRODUCT(--'Master List'!D3:D3000="ATL")*('Master List'!H3:H3000="Bonds")+('Master List'!D3:D3000="Bonds") However, that is again returning a #VALUE error. Any other ideas? "T. Valko" wrote: I'm assuming you mean that Bonds could appear in either column. If it appears in either column then count it. If it appaears in *both* columns count it a single appearance. =SUMPRODUCT(--('Master List'!$D$3:$D$3000="ATL"),('Master List'!$H$3:$H$3000="Bonds")+('Master List'!$I$3:$I$3000="Bonds")) -- Biff Microsoft Excel MVP "king60611" wrote in message ... I'm trying to get a count of how many times a value appears in either of 2 columns vs. a set column. In plain English, how many times does Bonds appear as the value in either Content 1 or Content 2 when the Office is ATL. I thought I had it using the conditional sum wizard and converting that to a COUNT, but it's only calculating true/false. I also tried SUMPRODUCT, but that gave me a #VALUE error. Here is the COUNT formula I had: =COUNT(IF('Master List'!$D$3:$D$3000="ATL",IF('Master List'!$H$3:$H$3000="Bonds",IF('Master List'!$I$3:$I$3000="Bonds",1,0),0),0)) I'm sure I've only been a keystroke or two away from getting this right, but I just can't figure it out. Thanks for your help. How do I return the How can I write this to return the total |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count unique values if 2 variables | Excel Worksheet Functions | |||
How to count dates with multiple values? | Excel Discussion (Misc queries) | |||
Functions/Formulas to count multiple variables | Excel Worksheet Functions | |||
How do I count values across multiple columns? | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |