• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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)
0
KCTS
Asked:
KCTS
  • 4
  • 4
1 Solution
 
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
NBVCCommented:
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now