Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default use month in cell date to sum all relevant cells

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default use month in cell date to sum all relevant cells

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)


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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default use month in cell date to sum all relevant cells

Thank you Claus

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

Chris
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Month and year from Cell containing date, month, year & time Montenegro Mick Excel Worksheet Functions 2 May 2nd 12 03:17 AM
Adding up relevant cells James Excel Discussion (Misc queries) 2 December 2nd 09 05:49 PM
Formula for cells containing last month's date Victor Delta[_2_] Excel Discussion (Misc queries) 6 August 3rd 08 12:04 AM
Remove cells with text not relevant Paal Excel Worksheet Functions 2 September 1st 05 07:08 PM
Calculate month-end date from date in adjacent cell? Matt D Francis Excel Worksheet Functions 4 May 19th 05 04:55 AM


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"