Dragging formulas on same sheet (automatic calculations turned on)

[Chris_86]

Really need some help as I have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell)
When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

Is there an easier alternative then manually changing each number?

 

[dermie_72]

Here is a link that might help, not just with this question, but some that you may not have considered yet.

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

 

[Chris_86]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	With Job?		Region						Location of Exposure
2	Yes	No	Northern		Central		Southern		Cairns		Brisbane		Townsville		Mackay		Rockhampton
3							#VALUE!		1				1
4

<TBODY>
</TBODY>

Statistics
​

Formulas used in I3 - =COUNTIFS('A1'!$G$4:$J$5,Statistics!I2)+COUNTIFS('A2'!$G$4:$J$5,Statistics!I2)
This works and counts every sheet providing I add the sheets manually e.g. A1, A2, A3 etc

Formulas used in G3 - =COUNTIFS('A1:A3'!$C$4:$D$5,Statistics!G2)
This doesn't work

G3 Formula does not work, however this sort of short cut rather then adding every sheet in like with I3 would be helpful. Is there a way I can use A1:A3 or another formula to capture the information.

This is Page A1
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	CONTACT DETAILS
2	STILL WITH JOB		YES		D.O.B.		2/04/1986		PREFERED CONTACT NUMBER		44444444		E-MAIL		joe.blogs@hotmail.com
3
4	REGION		Northern		LOCATION		Cairns				CURRENT POSITION		Manager
5

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

A1​

Im sorry for posting so much its just that I have spent the last 4 months working on this really great register (note the above is not the register) and my boss has told me that If i cannot capture the data I cannot use it.

 

[Aladin Akyurek]

COUNTIFS('A1:A3'!$C$4:$D$5,Statistics!G2)

is indeed not a legal formula. Assuming that the formula must be entered in the Statistics sheet, you'd need something like:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A3&"'!C4:D5"),G2))

See for more:

http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[Chris_86]

Thank you Aladin I will play around with this.

Cheers