Link to home
Start Free TrialLog in
Avatar of Brian Pierce
Brian PierceFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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)
Avatar of NBVC
NBVC
Flag of Canada image

Try:

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

=SUM(IF(MONTH(A3:AA3)=10,A5:AA5))
Enter using [ctrl]+[shift]+[enter]
Avatar of Brian Pierce

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your help - much appreciated :-)
opps - one slight problem - the average includes blank cells - how can I exclude blank cells?
Try:

=SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10),A5:AA5)/ SUMPRODUCT(--(A5:AA5<>""),--(MONTH(A3:AA3)=10))
Once again many thanks
You are welcome.