SUMPRODUCT with AND

  • Thread starter Thread starter Mike
  • Start date Start date
M

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










.
Click to expand...
 
Back
Top