Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Month and year from Cell containing date, month, year & time | Excel Worksheet Functions | |||
Adding up relevant cells | Excel Discussion (Misc queries) | |||
Formula for cells containing last month's date | Excel Discussion (Misc queries) | |||
Remove cells with text not relevant | Excel Worksheet Functions | |||
Calculate month-end date from date in adjacent cell? | Excel Worksheet Functions |