Dynamically updating formula

tpitt813

New Member
Joined
Oct 20, 2006
Messages
2
In the displayed sheet, I would like to update the formulas in the range c3:d8 with the appropriate cell range based on the month entered in C1. This is the formula in C3
=SUMPRODUCT(('Circ Chrgs'!$H$2:$H$240="BR")*('Circ Chrgs'!$I$2:$I$240="RING")*('Circ Chrgs'!$N$2:$N$240))
These are the 'month' references
jan Circ Chrgs'!$t$2:$t$240
feb Circ Chrgs'!$u$2:$u$240
mar Circ Chrgs'!$v$2:$v$240
apr Circ Chrgs'!$w$2:$w$240
may Circ Chrgs'!$x$2:$x$240
jun Circ Chrgs'!$y$2:$y$240
jul Circ Chrgs'!$n$2:$n$240
aug Circ Chrgs'!$o$2:$o$240
sep Circ Chrgs'!$p$2:$p$240
oct Circ Chrgs'!$q$2:$q$240
nov Circ Chrgs'!$r$2:$r$240
dec Circ Chrgs'!$s$2:$s$240

I will appreciate any help.
DSS Circuit Charges.xls
ABCDEF
1jul
2POPSITERING COSTBACK COSTTAIL COUNTTAIL SHARE
3BR12,872.7026,760.65155255.70
4LAF9,765.551,901.5030388.90
5MON10,099.201,976.5033365.93
6NO0.0010,050.0034295.59
7SH9,760.852,001.5021560.11
8WDL840.000.003424.71
Calc
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
Try...

C3, copied down and across:

=SUMPRODUCT(--('Circ Chrgs'!$H$2:$H$240=$B3),--('Circ Chrgs'!$I$2:$I$240=C$2),INDEX('Circ Chrgs'!$N$2:$Y$240,0,MATCH($C$1,{"Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun"},0)))

Hope this helps!

Edited formula...
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
Thanks a million. This works like a charm!

Your very welcome! Notice, though, that I've edited the formula so that the formula can be copied down the column and across...
 

Forum statistics

Threads
1,137,207
Messages
5,680,194
Members
419,888
Latest member
Prasad K

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top