Count w/ multiple variables & text values

[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

 

[T. Valko]

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"))

 

[king60611]

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'!D33000="ATL")*('Master
List'!H3:H3000="Bonds")+('Master List'!D33000="Bonds")

However, that is again returning a #VALUE error. Any other ideas?

 

[T. Valko]

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")))

 

[king60611]

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]

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!