sumproduct - three variables

I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. Any suggestions? The additional variable is in column G.

Thanks for any help.

SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)

You mean column G has to be a specific value? try something like this where that value is "x"

=SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),--($G$3:$G$1015="x"),D$3:D$1015)

if the value is numeric don't use quotes

Thanks.

I tried it, but i must be doing something wrong. Here's what I did:

SUMPRODUCT(--($A$3:$A$1015>=$A1027),--($A$3:$A$1015<=$B1027),--($A$3:$A$1015="a"),D$3:D$1015)

Can you see if I did something incorrectly?

Hi

You seem to be mixing data types in column A. You indicate that column A contains dates, but then you want to look for a text value ("a").

Is this what you are really trying to do?

rylo

Yes, that is what I am trying to do.

Aren't you looking for an "a" in column G, that was the column you mentioned in your first post.

That last formula will always give zero because the value in column A can't possibly satisfy all of the conditions, i.e. it can't be a date in your range and = "a"

Are you specifiying an incorrect column or do you want the column D values to be summed if column A has a date in the range or an "a"? If it's the latter try

=SUMPRODUCT(($A$3:$A$1015>=$A1026)*($A$3:$A$1015<=$B1026)+($A$3:$A$1015="a"),D$3:D$1015)

That still doesn't work. Can you not use three variables with sumproduct?

Actually THAT did work.

Thanks so much!!!

Originally Posted by nattasiray

When I'm use SUMPRODUCT, I will not Use "-" sign Because "-" mean NOT, "--" MEAN "Equal"

Sometime the "-" will interfere calculation (Still now I have question in my mine and can not find out why the "-" interfeere calucation.) So I will use the (*)

Please Login or Register  to view this content.

Hello nattasiray,

I'm not proposing using - anywhere, just --

-- acts as a "co-ercer" to convert TRUE/FALSE arrays to 1/0. Your proposed amendment, just alters the way the result is calculated by implicitly co-ercing the arrays (by multiplying them together).

There aren't any circumstances in which that amendment would change the result, unless D3:D1015 contains text, in which case your proposed formula will return an error.....