Excel Formula to Selectively Sum Data

I have a sheet that has text in column A, a amounts in column B and dates in column C. In row 1, columns O through Z I have the names of Months (January..December).
In the January column, row 2 (cell O2), I want to create a formula that will SUM all values in column B where MONTH(C#) = 1. Likewise in cell P2, I want to sum all rows in column B where MONTH(C#) = 2. And so on.

The result is 12 formulas, one for each month, that displays the total amount in colukmn B for that month (or 0 if nothing exists yet for the month).
LVL 15
dbbishopAsked:
Who is Participating?
 
leptonkaConnect With a Mentor Commented:
You can use this formula in O2 and copy across columns:

=SUMPRODUCT(--(MONTH($C$2:$C$6)=COLUMN()-COLUMN($O1)+1),$B$2:$B$6)

(rows from 2 to 6 - you can change)

(nb: "mmmm" in TEXT formula is language-dependent, it will not work in some non-english environment.)

Cheers,
Kris
0
 
barry houdiniConnect With a Mentor Commented:
Try this formula in O2 copied across

=SUMPRODUCT((TEXT($C$2:$C$100,"mmmm")=O$1)+0,$B$2:$B$100)

assumes data in rows 2 to 100, adjust as required

regards, barry
0
 
dbbishopAuthor Commented:
Both, in my case work and produce the same results. I've accepted Kris' as the best solution as he does take language into consideration, even though I am not considering running this code in a computer with Chinese set as the language any time in the near future, it may help others. I've split the points evenly. Thanks to both of you.
0
 
leptonkaCommented:
Thank you!
The problem is not just Chinese but for example my Hungarian, where "hhhh" stands for "mmmm"
Cheers,
Kris (she :-)
0
 
barry houdiniCommented:
It's a fair point :)

You could possibly use COLUMNS function to avoid having 2 COLUMN functions, i.e.

=SUMPRODUCT(--(MONTH($C$2:$C$6)=COLUMNS($O2:O2)),$B$2:$B$6)

regards, barry
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.