I was Googling how to count cells that meet multiple criteria and found an answer:
{=SUM((A1:A10=5)*(B1:B10=3))}
This worked fine, but when I substitute the 5 or 3 for a cell reference, it stops working. I've looked for an answer and tried a stack of different things but I can't get it to work with a cell reference. Can it be done? What am I doing wrong?
Last edited by cricket_stoner; 05-31-2010 at 06:16 AM.
when you modify the formula are you remembering to commit the changes with CTRL + SHIFT + ENTER ?
the above is in Array and confirming with Enter alone will not be sufficient.
If you prefer to avoid the CTRL + SHIFT + ENTER key press you can revert to SUMPRODUCT (no more efficient)
=SUMPRODUCT(--(A1:A10=5),--(B1:B10=3))
confirmed with ENTER
(if using XL2007 see COUNTIFS)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Referencing to DO solution you might use cell reference instead of number so you won't need change formula every time:Originally Posted by DonkeyOte
=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2))
where you can change values in C1 and C2
Yeah I was array-entering the formula with just that one change... that's why I'm a bit bemused as to why it didn't work.
I changed this:
{=SUM((A1:A10=5)*(B1:B10=3))}
to this:
{=SUM((A1:A10=F12)*(B1:B10=3))}
where F12=5 and it just returned 0.
I'll give your alternative formula a try. Thanks DonkeyOte.
Changing the Array from constant to cell reference should not make any difference... I suspect F12 is not a true number, ie:
=ISNUMBER(F12)
returns FALSE ?
If so:
=SUM((A1:A10=0+F12)*(B1:B10=3))
confirmed with CTRL + SHIFT + ENTER
FWIW I generally prefer SUMPRODUCT to Arrays if only for the standard Entry, however, the latter does have some significant advantages over the former which can make them more logical on occasion.
I seem to be having the same issue with that formula too. With numbers it works but with cell references it doesn't.
Yeah, it returns FALSE. I tried changing the format before but that didn't help.
The 0+ thing seems to work. I remember I tried something similar earlier when I was Googling all over the place, but apparently it wasn't exactly the same.
Not a true number, huh? I wonder what's going on there...
EDIT: below posted in response to post # 6
Note my prior post - I suspect you're cell references contain numbers stored as text rather than true numbers - see the coercion technique outlined previously.
Hmm... I just overtyped it with the same number after changing the format and it now says TRUE. I think it may have been on a text format before but I changed it a while ago. Didn't realise I'd have to manually reenter it. Thanks for straightening all that out for me.
We would need to see the formula...
...if it's related to say batting high scores it's feasible it would return a text string (ie 20*) ... if not then you should find you can coerce the result directly.
edit: too slow again
if the cell was formatted as Text prior to data entry then yes, that would be a cause.
Nah it's related to batting positions which are just numbers, although I think I had the format set to text because the first one is 1-2. I changed it but it didn't help until I manually typed over it.
Thanks DonkeyOte. Appreciate it.
Last edited by cricket_stoner; 05-31-2010 at 06:15 AM. Reason: To thank the marvellous DonkeyOte.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks