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


"srain001" wrote:

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

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

--
Gary's Student
gsnu200704


"srain001" wrote:

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

close

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

--
Don Guillett
SalesAid Software

"srain001" wrote in message
...
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

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.


srain001 wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson

You need to fix your parentheses:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"srain001" wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson

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.

srain001 wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"Dave F" wrote:

You need to fix your parentheses:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"srain001" wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"srain001" wrote:

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

"Dave F" wrote:

You need to fix your parentheses:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"srain001" wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson

BINGO!!!!

Thanxs so much!

Steph
"Dave Peterson" wrote:

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.

srain001 wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson


--

Dave Peterson

Is there any reason why you don't just concatenate both fields and
then use a countif function?


srain001 wrote:
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

"Dave F" wrote:

You need to fix your parentheses:

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"srain001" wrote:

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

"Dave Peterson" wrote:

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.


srain001 wrote:

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

--

Dave Peterson