asked on

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).

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

leptonka

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

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.

leptonka

Thank you!
The problem is not just Chinese but for example my Hungarian, where "hhhh" stands for "mmmm"
Cheers,
Kris (she :-)

barry houdini

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