hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Hi Cif,

And what is the problem you have got ?

Carim

That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Bob,

I see that the poster used semicolon. I don't think we can use semicolon?? So far, I have only seen , and * (multiplier).

Please clarify. Thank you.

By the way, did you catch my post mentioning SUMPRODUCT fan club? <g

Epinn

"Bob Phillips" wrote in message ...
That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Epinn,

That's the problem with you yanks, you don't know there is a big wide world
out there <G.

In many of the continental countries, they use a comma as a decimal
separator, instead of a dot as you (and we in the UK) do. Because of this I
guess that MS decided that they could not use comma as a separator in a
function, so they used semi-colon. So it is nothing to do with SP et al, it
is just the settings of the user.

Didn't see that SP thread, what is the subject?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I see that the poster used semicolon. I don't think we can use semicolon??
So far, I have only seen , and * (multiplier).

Please clarify. Thank you.

By the way, did you catch my post mentioning SUMPRODUCT fan club? <g

Epinn

"Bob Phillips" wrote in message
...
That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

i get the "#value" error

"Carim" wrote:


Hi Cif,

And what is the problem you have got ?

Carim

Yes, there is a big wide world out there and I should do more traveling. Are you in the suburb? May be I'll visit you one day.

Is that it again? I am aware of the difference in currency, time, date etc. But I thought that it would be universal for formulas. I don't see any reason why MS can't allow comma in SUMPRODUCT despite of a different currency setting. They just love to confuse *me*. ;)

So tell me, in order for my SUMPRODUCT formula not to have an error using semicolon, what settings do I change? Currency? Very curious.

I have this "urge" to promote SUMPRODUCT once a day. <g

Biff straightened me out on SUMIF and I am thankful for the info.

Epinn

"Bob Phillips" wrote in message ...
Epinn,

That's the problem with you yanks, you don't know there is a big wide world
out there <G.

In many of the continental countries, they use a comma as a decimal
separator, instead of a dot as you (and we in the UK) do. Because of this I
guess that MS decided that they could not use comma as a separator in a
function, so they used semi-colon. So it is nothing to do with SP et al, it
is just the settings of the user.

Didn't see that SP thread, what is the subject?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I see that the poster used semicolon. I don't think we can use semicolon??
So far, I have only seen , and * (multiplier).

Please clarify. Thank you.

By the way, did you catch my post mentioning SUMPRODUCT fan club? <g

Epinn

"Bob Phillips" wrote in message
...
That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Now it seems to work perfect, 10x :)

"Bob Phillips" wrote:

That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Bob,

I have a question on coercing.

Cif had an error (#value) earlier when the last array was coerced. When he used your formula which took out the double negating, it worked.

If I remember correctly, I read that it should still work if we include the double -- even though we don't need them. My experience told me the same as well. Am I missing something here?

Thanks.

Epinn

"Bob Phillips" wrote in message ...
Epinn,

That's the problem with you yanks, you don't know there is a big wide world
out there <G.

In many of the continental countries, they use a comma as a decimal
separator, instead of a dot as you (and we in the UK) do. Because of this I
guess that MS decided that they could not use comma as a separator in a
function, so they used semi-colon. So it is nothing to do with SP et al, it
is just the settings of the user.

Didn't see that SP thread, what is the subject?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I see that the poster used semicolon. I don't think we can use semicolon??
So far, I have only seen , and * (multiplier).

Please clarify. Thank you.

By the way, did you catch my post mentioning SUMPRODUCT fan club? <g

Epinn

"Bob Phillips" wrote in message
...
That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

"Epinn" wrote in message
...

Yes, there is a big wide world out there and I should do more traveling.
Are you in the suburb? May be I'll visit you one day.


Suburb? We use the word, but it is not really prevalent here. I live in a
medium size town (in your country they would call it a city, but citries
here are more than a million people, or a cathedral (which I think should be
the only definition)) on the beautiful south coats in the glorious land of
Wessex, the finest part of the British Isles bar none.

Is that it again? I am aware of the difference in currency, time, date
etc.
But I thought that it would be universal for formulas.
I don't see any reason why MS can't allow comma in SUMPRODUCT
despite of a different currency setting. They just love to confuse *me*.
;)


Better to confuse you than SP. How do you think it might raect if it saw?

=SUMPRODUCT(--(A1:A10=123,45),--(B1:B10="abc"),C1:C10)

Is that first comma for a number or for a separator?


So tell me, in order for my SUMPRODUCT formula not to have an
error using semicolon, what settings do I change? Currency? Very
curious.

WindowsControl PanelRegional SettingsRegional OptsionsCustomizeNumbers
and then set the List Separator

No, I agree with you, it should have worked anyway. The last -- was
unnecessary, but not an error.

In my tests it certainly worked okay.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I have a question on coercing.

Cif had an error (#value) earlier when the last array was coerced. When he
used your formula which took out the double negating, it worked.

If I remember correctly, I read that it should still work if we include the
double -- even though we don't need them. My experience told me the same as
well. Am I missing something here?

Thanks.

Epinn

"Bob Phillips" wrote in message
...
Epinn,

That's the problem with you yanks, you don't know there is a big wide world
out there <G.

In many of the continental countries, they use a comma as a decimal
separator, instead of a dot as you (and we in the UK) do. Because of this I
guess that MS decided that they could not use comma as a separator in a
function, so they used semi-colon. So it is nothing to do with SP et al, it
is just the settings of the user.

Didn't see that SP thread, what is the subject?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Bob,

I see that the poster used semicolon. I don't think we can use semicolon??
So far, I have only seen , and * (multiplier).

Please clarify. Thank you.

By the way, did you catch my post mentioning SUMPRODUCT fan club? <g

Epinn

"Bob Phillips" wrote in message
...
That works fine, but can be simplified a tad

=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);I1:I12)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Cif" wrote in message
...
hi, i am interested doing something like this:
=SUMPRODUCT(--(B1:B12="criteria 1");--(M1:M12=B29);--(I1:I12))

where B29 represent a value that i introduce in cell b29

thanks in advance

Before I go visit you, I better brush up my *Oxford* English. Did I say this one right? You say "lift" and not "elevator,"; "dear" instead of "expensive," right? I better stop here or MS and others may complain that I have turned this forum into an "English" forum. ;)

Better to confuse you than SP. How do you think it might react if it saw?

=SUMPRODUCT(--(A1:A10=123,45),--(B1:B10="abc"),C1:C10)

Now, I totally understand. I think it might "crash" or ...... :( Guess what, I finally got evaluate formula to crash. I got the hint from Biff as to which formula can make it happen.

Ah, digit grouping *symbol*, right above the digit grouping I mentioned the other day when we dealt with Baht. Wonder how your investigation went.

Thank you for enlightening me. Now I am going to dream about the British Isles.

Epinn


"Bob Phillips" wrote in message ...

"Epinn" wrote in message
...

Yes, there is a big wide world out there and I should do more traveling.
Are you in the suburb? May be I'll visit you one day.


Suburb? We use the word, but it is not really prevalent here. I live in a
medium size town (in your country they would call it a city, but citries
here are more than a million people, or a cathedral (which I think should be
the only definition)) on the beautiful south coats in the glorious land of
Wessex, the finest part of the British Isles bar none.

Is that it again? I am aware of the difference in currency, time, date
etc.
But I thought that it would be universal for formulas.
I don't see any reason why MS can't allow comma in SUMPRODUCT
despite of a different currency setting. They just love to confuse *me*.
;)


Better to confuse you than SP. How do you think it might raect if it saw?

=SUMPRODUCT(--(A1:A10=123,45),--(B1:B10="abc"),C1:C10)

Is that first comma for a number or for a separator?


So tell me, in order for my SUMPRODUCT formula not to have an
error using semicolon, what settings do I change? Currency? Very
curious.

WindowsControl PanelRegional SettingsRegional OptsionsCustomizeNumbers
and then set the List Separator