What's gone wrong with this one?

Hi

I picked up from some thread here the following formula,

=SUMPRODUCT(--(A1:A100="TRUE"),--(B1:B100="1"),C1:C200)

which seemed to me the right one for summing up the figures in Column C that appear on the rows where there are both a TRUE in col. A and a "1" in col. B (the "1" being the substitute for "TRUE").

Now I'm having second thoughts, (and even suspecting it might be intended for another purpose), for after having dealt with the translation hassle, replaced the commas etc, it returned VALUE!!!

So I tried it on my English Excel, where it kept returning
0, zero, and that is far too little.

Anyone familiar with this problem, or having any ideas of what might have gone wrong? Or positively sure it is the wrong formula?

Appreciate any help
bcb

The formula that was returning 0 would be correct it was just not finding one of the criteria. The problem could be that the information in Column B is entered as a number and not as text (Making it so "1" should be replaced by 1) and "TRUE" might need to be replaced as TRUE.

Try going back to your original formula and making those 2 changes, 1 at a time and see if that fixes your problem.

Hope that helps..
John

Thank you all!

Seems it was the one combination I hadn't tried (with/without "") that worked.
In this case:
TRUE without any q.marks
and a couple around the "1".

Why is that, by the way?

And this formula is possibly suited to expand, I suppose (and hope), to count with conditions in several more columns?

Not ungrateful or insatiable, just curious.
bcb

Originally Posted by BCB

Thank you all!

Seems it was the one combination I hadn't tried (with/without "") that worked.
In this case:
TRUE without any q.marks
and a couple around the "1".

Why is that, by the way?

And this formula is possibly suited to expand, I suppose (and hope), to count with conditions in several more columns?

Not ungrateful or insatiable, just curious.
bcb

Think of TRUE / FALSE as 'recognised' conditions and not as words.

Is your '1' a text item? If not there should have been no reason for puttinq quotes around the criteria.

Bob Phillips explanation at http://www.xldynamic.com/source/xld.SUMPRODUCT.html would be the guide to expanding this.

hth
---

Hello again

In my case it just won't play without the ""'s around the 1 (the 1 being a substitute for TRUE, that is, and thereby a recognized condition, as you say).
But no biggie - I got it to work somehow (with the combination I described above).
And thanks anyway for the link. I'll have to look further into that on occasion.
BCB

Originally Posted by BCB

Hello again

In my case it just won't play without the ""'s around the 1 (the 1 being a substitute for TRUE, that is, and thereby a recognized condition, as you say).
But no biggie - I got it to work somehow (with the combination I described above).
And thanks anyway for the link. I'll have to look further into that on occasion.
BCB

Can you copy to a test sheet and post the bit that isn't working? (save the test.xls and (in explore) rightmouse Add to Archive, rename as .zip and post here as Attachment), sounds like fun

---

Hi again

I am prevented from downloading and copying from this office (at work), but I can write the formula, though:
First, the one that functions:
=SUMPRODUCT(--(W37:W649=TRUE);--(T37:T649="1");Q37:Q649)
The dysfunctional one is the same, without the ""s.
=SUMPRODUCT(--(W37:W649=TRUE);--(T37:T649=1);Q37:Q649)

Probably a language issue or something.
Come to think of it, my first SUMPRODUCT-formula wouldn't work until I had it run on my English Excel, which also is a more recent version of Excel than the one I work on regularly.
And - dont get me wrong: the first formula serves its purpose perfectly, and opens up a field of long neglected work.
Thanks again
bcb

Hello, again

No, I'm not on a Mac.

The language in question is Norwegian, and the Excel on that computer is not quite up to date, either, so I doubt there's any great benefit in converting it to English, although I think I will one of these days. If only to eliminate that possible cause.
Anyway, I'll stick to the "If it ain't broke, don't fix it"-policy with the SUMPRODUCT-formula.
But I'll nevertheless check out your version with the multiplying asterisk.
Thanks
BCB

Originally Posted by BCB

Hello, again

No, I'm not on a Mac.

The language in question is Norwegian, and the Excel on that computer is not quite up to date, either, so I doubt there's any great benefit in converting it to English, although I think I will one of these days. If only to eliminate that possible cause.
Anyway, I'll stick to the "If it ain't broke, don't fix it"-policy with the SUMPRODUCT-formula.
But I'll nevertheless check out your version with the multiplying asterisk.
Thanks
BCB

'If it ain't broke' sounds good to me, and Thanks for the response. I would doubt that 'English' (the American or the real version) would solve your problem, it was more just a check that the semi-colon ; was being used as a Multiply and not as a Comma, both of which are useable in sumproduct. (and yes, I know you can't use the comma option with the double unary, but the brain doesn't always kick into the correct gear when the mouth opens, and I missed that point when asking)

'don't fix it'

---