|
Formula help
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 |
Formula help
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 |
Formula help
=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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
Formula help
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 |
| All times are GMT +1. The time now is 11:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com