Can't get SUMIF to do what I want

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)
LVL 70
KCTSAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
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
 
NBVCCommented:
Try:

=SUMPRODUCT(--(MONTH(A3:AA3)=10),A5:AA5)
0
 
SteveCommented:
you can use SUM and IF...

=SUM(IF(MONTH(A3:AA3)=10,A5:AA5))
Enter using [ctrl]+[shift]+[enter]
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
KCTSAuthor Commented:
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
 
KCTSAuthor Commented:
Thanks for your help - much appreciated :-)
0
 
KCTSAuthor Commented:
opps - one slight problem - the average includes blank cells - how can I exclude blank cells?
0
 
NBVCCommented:
Try:

=SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10),A5:AA5)/ SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10))
0
 
KCTSAuthor Commented:
Once again many thanks
0
 
NBVCCommented:
You are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.