Count w/ multiple variables & text values

  • Thread starter Thread starter king60611
  • Start date Start date
K

king60611

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
 
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"))
 
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?
 
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")))
 
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.
 
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!
 
Back
Top