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
Doug BishopDatabase DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
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
leptonkaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperAuthor 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.
leptonkaCommented:
Thank you!
The problem is not just Chinese but for example my Hungarian, where "hhhh" stands for "mmmm"
Cheers,
Kris (she :-)
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.