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"))

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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

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" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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

=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


--
Biff
Microsoft Excel MVP


"Lawrence" wrote in message
...
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" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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

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

"T. Valko" wrote:

=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


--
Biff
Microsoft Excel MVP


"Lawrence" wrote in message
...
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" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Lawrence" wrote in message
...
Thanks so much!! you helped me simplify a formula I created on my
spreadsheet!

"T. Valko" wrote:

=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


--
Biff
Microsoft Excel MVP


"Lawrence" wrote in message
...
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" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"Curtis" wrote in message
...
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