Brian Pierce
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)
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"
you can use SUM and IF...
=SUM(IF(MONTH(A3:AA3)=10,A 5:AA5))
Enter using [ctrl]+[shift]+[enter]
=SUM(IF(MONTH(A3:AA3)=10,A
Enter using [ctrl]+[shift]+[enter]
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help - much appreciated :-)
ASKER
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(A 3:AA3)=10) )
=SUMPRODUCT(--(A5:AA5<>"")
ASKER
Once again many thanks
You are welcome.
=SUMPRODUCT(--(MONTH(A3:AA