SUMIF ?

[VulcanJedi]

I had a colleague ask if I could help w/ a task, although I could immediately think of a few ideas and found a solution, I am positive a simpler/cleaner one exists.

?:
2 columns (cost and date)
I want to sum costs which are within a date, specifically a month.
ie all costs with a date in january attributed, sum...etc.
reiterate across all months.

I tried SUMIF and it didn't work, I used, the range as described in help feature but I think I missing somehting? I was forced to write code instead. Tranpkp

 

[Dreamboat]

See if this helps, Tran? Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros

 

[xlbo]

Cost in A1:A100
Dates in B1:B100

formula would be:
=SUM((MONTH(B1:B100)=1)*(A1:A100))
enter using CTRL + SHIFT + ENTER - you will see { } round the formula if you have done this correctly Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[combo]

A pivot table can be a tool for quick summing. Assuming you have table with headers 'date' and 'cost', select any cell in table and go Data>Pivot table.
When you are in step 3 of the wizard, click l'layout', drag 'date' to row field and 'cost' to data field. Double-click 'cost' and customize it (in totals should be sum, can set number format etc.
Accept all settings, choose the place for report and close wizard.
To group dates, right-click 'date field in table, and select option 'grouping' from the context menu. Select months and years if necessary. (NB. grouping is impossible if there are some empty dates).

 

[VulcanJedi]

Thanks for the input, however none of these solutions appear to be working. Geoff, I tried the CTRL+SHIFT+ENTER thing (what is that?) ...didn't work. The formula looks like I tried previously. Cureently I can only think of VBA code / custom function to perform this task, yet I feel its unnecessary. The sum keeps calculating to ZERO
Does it matter it other data lies in the columns (ie blanks, or text ie: 'pending') or anything? I figured that would calculate to false and not SUM
The pivot table, idea is interesting, never even came to mind, but how to you reference the month ranges in the dates? Tranpkp

 

[combo]

As for pivot table - first you have it with two columns, first - dates, second - sums for each date. To group raw dates by month, right-click the field 'date' in pivot table. From the context menu choose 'grouping' (access also from pivot table toolbar). You can choose grouping options for dates, including first and last day, years, quarters days etc. It is possible to use two or more time units.
Hope you will success.

 

[xlbo]

Any text that cannot be evaluated to a month will return a VALUE error. Also, MONTH(a1) where A1 = "" will return a 1 so, you will need:
=SUM((IF(ISERROR(MONTH(B1:B10)),0,IF(B1:B10="",0,IF(MONTH(B1:B10)=1,1,0))))*(A1:A10))
entered with CTRL+SHIFT+ENTER (this indicates an ARRAY formula) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Dreamboat]

Hmm...guess I forgot to post my link. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros

 

[xlbo]

Was gonna ask about that but I thought that you meant the link to TheOfficeExperts.com
Must've been too busy feeding the wooden horse ;-) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur