Formula help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to create a formula to count information located in two separate
columns but appear in the same row. For example:

Column A Column B
A 1
B 2
C 3
D 4

What I'm trying to do is count every instance that "A" appears in the same
row as "4" at any given time. The current formula I'm working with is
=SUMPRODUCT(--$A$1:$A$4="A"),--($B$1:$B$4=4))

However, something must be wrong because I keep getting a count of 0 even
though I'm reference the right column and range.

Hope anyone can help!

Srain
 
Answer is correct as "D" is aligned with "4" not "A"

=SUMPRODUCT(--($A$1:$A$4="A"),--($B$1:$B$4=4))

Note there was bracket missing from your formula
 
Watch your ()'s:

=SUMPRODUCT(--($A$1:$A$4="A"),--($B$1:$B$4=4))

If this doesn't help, look for leading or trailing spaces in the values in
column A. And see if the values in column B are really numbers. You can use
=isnumber(b4) to see true/false.
 
Hi,

Thanx. I had the () at the right spot. My post was a typo....!! In any
case, I'm still not getting a proper count. I should have mentionned that
the values I'm counting are in another worksheet. Here's what I have:
=SUMPRODUCT(--(Sheet1$A$1:$A$4="A"),--($B$1:$B$4=4)

Does this make any sense?

Steph
 
You need to fix your parentheses:

=SUMPRODUCT(--(Sheet1$A$1:$A$4="A"),--($B$1:$B$4=4))

Dave
 
Nope.

maybe...

=SUMPRODUCT(--(Sheet1!$A$1:$A$4="A"),--($B$1:$B$4=4))
or
=SUMPRODUCT(--(Sheet1!$A$1:$A$4="A"),--(sheet1!$B$1:$B$4=4))

depending on where your data is.

If it still doesn't work, it's best to copy the formula from the formula bar and
paste it into the newsgroup post. Then typing errors won't be added.
 
Hi,

It still gives me a count of zero. I've input the formula exactly as
written, yet nothing. I'm at a loss... Columns are right, range is
right...I don't know what I'm doing wrong...

Here's the formula I'm using again. I don't see any errors...

=SUMPRODUCT(--(Sheet1!$A$1:$A$4="A"),--($B$1:$B$4=4))

Steph
 
Is it possible that the 4 in your data is formatted as text? If so, format
the numbers as numbers, not text.

Dave
 
BINGO!!!!

Thanxs so much!

Steph
Dave Peterson said:
Nope.

maybe...

=SUMPRODUCT(--(Sheet1!$A$1:$A$4="A"),--($B$1:$B$4=4))
or
=SUMPRODUCT(--(Sheet1!$A$1:$A$4="A"),--(sheet1!$B$1:$B$4=4))

depending on where your data is.

If it still doesn't work, it's best to copy the formula from the formula bar and
paste it into the newsgroup post. Then typing errors won't be added.
Click to expand...
 
Is there any reason why you don't just concatenate both fields and
then use a countif function?
 
Back
Top