Solved

Can't get SUMIF to do what I want

Posted on 2013-10-29
9
230 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with excell ... 6 58
excel pivot question 4 40
highlight duplicate entry 16 26
Export Query data to excel file 14 22
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

947 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now