SUMPRODUCT Questions

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

Mike

Hi. I had two questions about the following formula:

=SUMPRODUCT(A1:A20,B1:B20,
{1;0;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;1})

1. What does the {1;0;0 .... 0;1} do?
2. Is there a way to modify this statement so that it
will count the occurrences when the first array = "John"
and the second array has the number "0"?

Thanks,
Mike.
 
Hi Mike,
ad question 1):
{1,0.....} does the same you write the numbers in C1:C20 and the
formula:
=SUMPRODUCT(A1:A20,B1:B20,C1:C20)
array fomula finish with <ctrl><shift><return>

M@x
 
Hi Mike,
The {...}, is an array of values. Since A1:A20 is a column, we need a
vertical array hence the semicolons. A horizontal array uses commas to
separate items.

Your second question needs: =SUMPRODUCT(--(A1:A13="John"),--(B1:B13=0))

Season's Greetings
Bernard
 
Back
Top