Solved

Can't get SUMIF to do what I want

Posted on 2013-10-29
9
242 Views
Last Modified: 2013-10-30
I have a spreadsheet that has dates in the cells A3:AA3
In cell A5:AA5 are meter readings

I want to sum all the meter readings where the month the date is 10 (October) but I can't seem to get sumif to do the job its got to be something like this:

=SUMIF(A3:AA3,"Month()=10",A5:AA5)
0
Comment
Question by:KCTS
  • 4
  • 4
9 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39609344
Try:

=SUMPRODUCT(--(MONTH(A3:AA3)=10),A5:AA5)
0
 
LVL 24

Expert Comment

by:Steve
ID: 39609978
you can use SUM and IF...

=SUM(IF(MONTH(A3:AA3)=10,A5:AA5))
Enter using [ctrl]+[shift]+[enter]
0
 
LVL 70

Author Comment

by:KCTS
ID: 39610368
Thanks @NB_VC that works a treat - I hope you don't mind a couple of supplementary questions.

1. Can you explain what -- does - I've not seen that use before
2. How can I also calculate the average in the same scenario
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39610410
the -- coerces the results of True/False from the conditional decks to 1/0 so the math can be done.
to get average
=SUMPRODUCT(--(MONTH(A3:AA3)=10),A5:AA5)/ SUMPRODUCT(--(MONTH(A3:AA3)=10))
0
 
LVL 70

Author Closing Comment

by:KCTS
ID: 39611132
Thanks for your help - much appreciated :-)
0
 
LVL 70

Author Comment

by:KCTS
ID: 39611217
opps - one slight problem - the average includes blank cells - how can I exclude blank cells?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39611324
Try:

=SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10),A5:AA5)/ SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10))
0
 
LVL 70

Author Comment

by:KCTS
ID: 39612066
Once again many thanks
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39612083
You are welcome.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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