Multiple conditions

[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

 

[T. Valko]

Try this:

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

=SUMPRODUCT(--(MONTH(D1100)=11),--(F1:F100="X"),--(H1:H100="Y"))

 

[Lawrence]

Please forgive the somewhat newbie question, but in the formula below, what
are the two dashes for after the open parenthesis following sumproduct?

 

[T. Valko]

=SUMPRODUCT(--(MONTH(D1100)=11),--(F1:F100="X"),--(H1:H100="Y"))

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

(MONTH(D1100)=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

 

[Lawrence]

Thanks so much!! you helped me simplify a formula I created on my spreadsheet!

 

[T. Valko]

You're welcome. Thanks for the feedback!