Sumproduct mmddyyy just the month?

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

I have the date in column A mmddyyy and I need a sumproduct formula
to deliver the sales for Mike in December. Salesperson is in B and
sales is in C. How to I extract the month from culom A in the
sumproduct formula??

Thanks all!
 
wx4usa said:
I have the date in column A mmddyyy and I need
a sumproduct formula to deliver the sales for Mike
in December. Salesperson is in B and sales is in C.
How to I extract the month from culom A in the
sumproduct formula?
Click to expand...

That depends on what you mean by mmddyyy [sic].

If you have a date (serial number) that is formatted as mmddyyyy, you can
simply do:

=sumproduct((month(a1:a100)=12)*(b1:b100="mike"),c1:c100)

But if mmddyyyy is text, you might do:

=sumproduct((--left(a1:a100,2)=12)*(b1:b100="mike"),c1:c100)

On the other hand, if mmddyyyy is a number formatted as 00000000, the LEFT
expression above will not work for months less than 10, despite the format
to display the leading zero.

Instead, you might do:

=sumproduct((int(a1:a100/1000000)=12)*(b1:b100="mike"),c1:c100)
 
Back
Top