SUMPRODUCT with AND

[Mike]

Hi.

I use the following formula to count the number of times
the cells in column "A" are above the value of 1 and
column "B" is not empty. Is there a way to put another
condition on Column "A"? I'd like to know when the value
in column "A" is between the value of 1 and 7 and the
value in column "B" is not empty.

=SUMPRODUCT(--(Dec!A6:A20>1),--(Dec!B6:B20<>""))

I tried the following, but it does not work:

=SUMPRODUCT(--(AND(Dec!A6:A20>1,Dec!A6:A20<7)),--(Dec!
B6:B20<>""))

Thanks!
Mike.

 

[Dave R.]

There is no need to use 'and', you can just add another requirement to the
products that are going to be summed.

=+SUMPRODUCT(--(A1:A10<>""),--(A1:A10>1),--(A1:A10<8))

 

[Guest]

Just seperate the logic - SUMPRODUCT works as a set of AND statements s
=SUMPRODUCT((Dec!A6:A20>1)*(Dec!A6:A20<7)*(Dec!B6:B20<>"")

should work nicely - just amend >&< to <= &>= if you need to include 1s and 7s

 

[Dave R.]

Sorry, I always screw up these equal to greater than less than things;

=+SUMPRODUCT(--(A1:A10<>""),--(A1:A10>=1),--(A1:A10<=7))

also I just noticed you are looking to see if they are not blank, which is
unnecessary since they will only be summed if they fit the other conditions.

=+SUMPRODUCT(--(A1:A10>=1),--(A1:A10<=7))

 

[Dave R.]

Disregard that part, sorry I didnt see ur potentially blank range was
separate.

 

[Guest]

Thanks for your help!

-----Original Message-----
Just seperate the logic - SUMPRODUCT works as a set of AND statements so
=SUMPRODUCT((Dec!A6:A20>1)*(Dec!A6:A20<7)*(Dec!

 

[Mike]

Thanks, Dave! This worked nicely.

Mike.

-----Original Message-----
Sorry, I always screw up these equal to greater than less than things;

=+SUMPRODUCT(--(A1:A10<>""),--(A1:A10>=1),--(A1:A10<=7))

also I just noticed you are looking to see if they are not blank, which is
unnecessary since they will only be summed if they fit the other conditions.

=+SUMPRODUCT(--(A1:A10>=1),--(A1:A10<=7))










.