sumproduct with one variable criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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

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)
 
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)

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

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)
 
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

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)

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

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)
 
Now it seems to work perfect, 10x :)

Bob Phillips said:
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)
Click to expand...
 
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

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)

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

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)
 
Epinn said:
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.
Click to expand...

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*. ;)
Click to expand...

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
Click to expand...
curious.

Windows>Control Panel>Regional Settings>Regional Optsions>Customize>Numbers
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)

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

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)

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

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)
 
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. ;)

=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



Epinn said:
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.
Click to expand...

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*. ;)
Click to expand...

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
Click to expand...
curious.

Windows>Control Panel>Regional Settings>Regional Optsions>Customize>Numbers
and then set the List Separator
 
Back
Top