Two Questions

msfatman

Board Regular
Joined
Apr 10, 2008
Messages
110
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MRA

New Member
Joined
Jun 27, 2008
Messages
36
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 4, 2004
Messages
2,023
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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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