| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formula using sumproduct
A--------------B-
6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
Hi,
Remove the quotes from TRUE and no need to array enter =SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE)) Mike "ferde" wrote: A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
I guess that your TRUE and FALSE are not text strings but Boolean values.
Try deleting the quote marks around TRUE. In fact as a Boolean you can delete the =TRUE, so the formula simplifies to =SUMPRODUCT((A2:A6="6N")*(B2:B6)) -- David Biddulph "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
I knew I was getting close to solving this ....Thank you so much Mike.
...works great! "Mike H" wrote: Hi, Remove the quotes from TRUE and no need to array enter =SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE)) Mike "ferde" wrote: A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
If column B contains only the logical values TRUE or FALSE:
=SUMPRODUCT((A2:A6="6N")*B2:B6) -- Biff Microsoft Excel MVP "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
Ferde,
I dispair that Excel is so anal about data typing. If you have TRUE in a cell, and it's centered, (presuming you haven't set left, center, or right alignment), then you know that Excel has typed it as boolean. Your formula won't work because the quotes around "TRUE" means it's text data type, not boolean. If the TRUE in your cell had been text (such as with a preceding apostrophe, or having first been formatted for text (before TRUE was entered into the cell), then your formula would have worked. It's just one more gotcha. Similar problems occur when Excel has typed a cell as text, but it "looks" like a number. Happens frequently when numeric data has been pasted in, such as from a web page or other program. In that case, some formulas will pick up the numbers, and some won't. The nerds at Excel think users understand data typing, so they designed it with a programmer's approach, not with a typical end-user's perspective. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
Thank you works great
"T. Valko" wrote: If column B contains only the logical values TRUE or FALSE: =SUMPRODUCT((A2:A6="6N")*B2:B6) -- Biff Microsoft Excel MVP "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
Thank you very much for your reply
"David Biddulph" wrote: I guess that your TRUE and FALSE are not text strings but Boolean values. Try deleting the quote marks around TRUE. In fact as a Boolean you can delete the =TRUE, so the formula simplifies to =SUMPRODUCT((A2:A6="6N")*(B2:B6)) -- David Biddulph "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
I appreciate the explanation that will help me in the future from making
similiar mistakes. "Earl Kiosterud" wrote: Ferde, I dispair that Excel is so anal about data typing. If you have TRUE in a cell, and it's centered, (presuming you haven't set left, center, or right alignment), then you know that Excel has typed it as boolean. Your formula won't work because the quotes around "TRUE" means it's text data type, not boolean. If the TRUE in your cell had been text (such as with a preceding apostrophe, or having first been formatted for text (before TRUE was entered into the cell), then your formula would have worked. It's just one more gotcha. Similar problems occur when Excel has typed a cell as text, but it "looks" like a number. Happens frequently when numeric data has been pasted in, such as from a web page or other program. In that case, some formulas will pick up the numbers, and some won't. The nerds at Excel think users understand data typing, so they designed it with a programmer's approach, not with a typical end-user's perspective. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "ferde" wrote in message ... Thank you works great "T. Valko" wrote: If column B contains only the logical values TRUE or FALSE: =SUMPRODUCT((A2:A6="6N")*B2:B6) -- Biff Microsoft Excel MVP "ferde" wrote in message ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formula using sumproduct
If values in column B are boolean values and not text values, you have to
use SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE)) -- Regards. Daniel "ferde" a écrit dans le message de news: ... A--------------B- 6N FALSE 6N FALSE 7N TRUE 8N TRUE 6N TRUE I am using this as an array formula in C2 but getting zero for an answer when the correct answer would be 1. Can anyone tell me what I am doing wrong. I am looking for the formula to count the number of times "TRUE" is found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly appreciated. SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE")) correct answer =1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| How best to use sumproduct instead of conditional sum: | Excel Worksheet Functions | |||
| How do I set up a conditional sumproduct formula (ie. sumifproduct | Excel Worksheet Functions | |||
| Conditional SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Conditional sumproduct? | New Users to Excel | |||
Linear Mode
