use month in cell date to sum all relevant cells

  • Thread starter Thread starter Chris Smith
  • Start date Start date
C

Chris Smith

Hi - I have a Sheet of orders entered in rows, order date is col D and order quantities in columns O through AM (1 product per column, if not required cell is blank). On a different sheet I am trying to sum all the orders for each product / month

I have entered
=SUMIF(MONTH('Sheet1'!$D$6:$D$1008),4,'Sheet1'!O$6:O$1008)
meaning if the month value in the cell is 4 (April) count any quantity in corresponding cell column O.

What have I done wrong please?

Thanks for looking
 
Hi Chris,

Am Tue, 16 Jul 2013 13:09:22 -0700 (PDT) schrieb Chris Smith:
=SUMIF(MONTH('Sheet1'!$D$6:$D$1008),4,'Sheet1'!O$6:O$1008)
Click to expand...

try:
=SUM(IF(MONTH(D6:D1008)=4,O6:O1008))
and enter the array formula with CTRL+Shift+Enter
or:
=SUMPRODUCT(--(MONTH(D6:D1008)=4),O6:O1008)


Regards
Claus B.
 
Thank you Claus

I should have thought of SUMPRODUCT - It is extremely useful and better than an array I think.

Chris
 
Back
Top