Macro to convert dates in a Conditional Sum Formulas?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have built a table with a wide array of conditional sum formulas as follows:

=SUM(IF($T$5:$T$1000="Mar",IF($D$5:$D$1000="Unit A",$K$5:$K$1000,0),0))

Basically, I want to add each Unit's numbers together by month. And the
formula works very well. However, as I have quite a few of these conditional
sums that I built with the Conditional Sum Wizard, I would like an easy way
of converting the months in each formula via a macro.

I have tried recording a macro but in order to get the formula to update
correctly I have to use the "Edit Macro" button by the formula bar to get the
formula to work corrctly with the change of months. Since the month is always
in the same position in the formula, I tried using "SendKeys" but I can't
seem to get the cursor to insert via macro the way I could with a mouse. My
only other alternative is to manually edit each formula and I have alot of
these Conditional Sums to edit.

Anybody know a better way? I'm stumped.
 
Hi
why not use 'Edit - Replace'

or even better. Why not use a cell reference. e.g.
=SUM(IF($T$5:$T$1000=A1,IF($D$5:$D$1000="Unit A",$K$5:$K$1000,0),0))

where A1 contains your month name.

You may also consider using a pivot table for this kind of reports
 
Back
Top