Multiple conditions

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

Column d = date (mm/dd/yyyy)....column contains multiple days in month,
months in year

Column f = widget

Column h = sales force

I need a formula that will calculate the monthly number of widgets by sales
force

Thanks
 
Try this:

Month = 11 (November)
Widget = X
Sales force = Y

=SUMPRODUCT(--(MONTH(D1:D100)=11),--(F1:F100="X"),--(H1:H100="Y"))
 
Please forgive the somewhat newbie question, but in the formula below, what
are the two dashes for after the open parenthesis following sumproduct?
 
=SUMPRODUCT(--(MONTH(D1:D100)=11),--(F1:F100="X"),--(H1:H100="Y"))

Each of these logical expressions will return an array of either TRUE or
FALSE:

(MONTH(D1:D100)=11)
(F1:F100="X")
(H1:H100="Y")

SUMPRODUCT works with numbers so we need to convert those TRUEs and FALSEs
to numbers. One way to do that is to use the "--".

The "--" coerces the TRUE to 1 and FALSE to 0.

Then, all 3 arrays are multiplied together and summed which gives us the
result of the formula. In this case, the result is a count.

It would look something like this:

1*1*1=1
0*1*1=0
1*0*0=0
1*1*1=1
1*1*0=0
0*0*0=0

=SUMPRODUCT({1;0;0;1;0;0}) = 2

See this for a comprehensive explanation of SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html
 
Back
Top