Solved

Sum by month

Posted on 2011-03-14
7
217 Views
Last Modified: 2012-05-11
I am trying to sum by month based on the attached spreadsheet.  I have tried searching for an answer to this, and when I use what I think is the correct array formula, I get a #Value error.

Thanks,

Michael expert-exchange-datesum-example.xlsx
0
Comment
Question by:paxtonm
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35129907
Which column? Can you clarify more please.
0
 

Author Comment

by:paxtonm
ID: 35129917
Sorry, I am trying to sum Column F.
0
 

Author Comment

by:paxtonm
ID: 35129963
I have been getting an error using this formula

=SUMPRODUCT((MONTH($A$2:$A$225)=MONTH(I1)*($B$2:$F$225)))
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35130015
Based on your workbook you would need:
=SUMPRODUCT((MONTH($A$2:$A$225)=MONTH(H2))*($F$2:$F$225))
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35130030
Safer to check the year too though, unless you will only have one year's data:
=SUMPRODUCT(--(MONTH($A$2:$A$225)=MONTH(H3)),--(YEAR($A$2:$A$225)=YEAR(H3)),$F$2:$F$225)

Note: you could also use a pivot table for a simple summary.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35130042
Not sure if you have correct columns in that formula:
Column I is empty.
$B$2:$F$225 this is a wide range.
Could you show a sample of what you expect the formula to do? I am not sure I got you correctly.
0
 

Author Closing Comment

by:paxtonm
ID: 35130050
Spot on formula!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Make a Cell act like a Date 7 39
Auto Populate Day Month  2 digit Date 4 19
formula how to get the number incrementor? 3 23
sort column using vba 2 18
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question