# Two Questions

#### msfatman

##### Board Regular
I'm using this formula to calculate data based over a 10 week period:-

=COUNTIF(Week1!J:J,H2)+COUNTIF(Week2!J:J,H2)+COUNTIF(Week3!J:J,H2)+COUNTIF(Week4!J:J,H2)+COUNTIF(Week5!J:J,H2)+COUNTIF(Week6!J:J,H2)+COUNTIF(Week7!J:J,H2)+COUNTIF(Week8!J:J,H2)+COUNTIF(Week9!J:J,H2)+COUNTIF(Week10!J:J,H2)

Can the formula be simplified?

Secondly, I need a macro to run when the cell contents of cells D1, F3 or G4 change. Could some one advise how I can do this?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### MRA

##### New Member
well, I'm not quite sure how to simplify the code you have, but I can answer your second question.
Use the worksheet change event, with an intercept condition:

Code:
``````Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("[your range here]")) Is Nothing Then
{code}
end if``````
that should do the trick

Mike

Last edited:

#### Scott Huish

##### MrExcel MVP
I think this will work for your formula:

=SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIRECT("1:10"))&"!J:J"),H2))

Last edited:

#### cornflakegirl

##### Well-known Member
Is there a reason why you can't keep all the data on the same worksheet (maybe with an additional field specifying the week)? If you need to analyse it all together, it would make life easier.

#### Peter_SSs

##### MrExcel MVP, Moderator
Another option. Assuming ...
a) the sheets Week1 to Week10 are consecutive sheets in the workbook.
b) cell K1 (any other cell will do) is vacant on each 'Week' sheets
c) the result sheet is called 'Summary'

... then try this.

1. Select Week1 sheet, then hold 'Shift' and select Week10 sheet. This should select all the 'Week' sheets
2. Select cell K1 and enter the formula
=COUNTIF(J:J,Summary!H2)
This should enter that formula on each of the 10 sheets.
3. Now select the 'Summary' sheet and enter the formula:
=SUM(Week1:Week10!K1)

Edit: Also, the suggested vba code to run when cells D1, F3 or G4 change, will only work if those cells are changed manually. If they contain formulas then you would need some code in the WorkSheet_Calculate event

Last edited:

#### msfatman

##### Board Regular
Thanks everyone that's made things a lot simpler.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,025
Messages
5,856,912
Members
431,837
Latest member
megantang

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

### Which adblocker are you using?

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

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