automate the formula - sum and countifs with unknown number of sheets.

[aleksandrae]

Hi,

I have formula as below ad its works. However I need to know in advance how many sheets I will have and then add it into formula by hand. Is there any solution to automate it? I've tried indirect formula but it does works (maybe I use it in wrong way).

=SUM(IFNA(COUNTIFS(INDEX('1*'!$C$2:$E$125,MATCH($A10,'1*'!$A$2:$A$125,0),MATCH(D$1,'1*'!$C$1:$E$1,0)),"p"),0),IFNA(COUNTIFS(INDEX('2*'!$C$2:$E$125,MATCH($A10,'2*'!$A$2:$A$125,0),MATCH(D$1,'2*'!$C$1:$E$1,0)),"p"),0),IFNA(COUNTIFS(INDEX('3*'!$C$2:$E$125,MATCH($A10,'3*'!$A$2:$A$125,0),MATCH(D$1,'3*'!$C$1:$E$1,0)),"p"),0))


Ps. I paste only the formula and jpg because I have a problem with copy/paste mini-sheet (you already try to help me)
thanks in advance,
Aleksandra

 

[jasonb75]

Are the ranges A2:A125 and C1:E1 in the exact same order on every sheet?

If they are then you could make things a lot easier by only doing the 2 matches on one sheet to identify the result cell, then do the rest from that.

 

[aleksandrae]

Are the ranges A2:A125 and C1:E1 in the exact same order on every sheet?

If they are then you could make things a lot easier by only doing the 2 matches on one sheet to identify the result cell, then do the rest from that.

ranges in column A are the same, but in main sheet in row 1 I have date from all month: 1.09; 2.09; 3.09 ... In sheets I add in range C1:E1 I have only 3 chosen data, e.g. sheet (1): 1.09; 5.09; 10.09; sheet (2): 4.09; 5,09; 11.09 ......
It looks like this:
main sheet:

name	sum	1.09	2.09	3.09	4.09	5.09	6.09	7.09	8.09	9.09	10.09	11.09	12.09	13.09	14.09	15.09	16.09	17.09	18.09	19.09	20.09	21.09	22.09	23.09	24.09	25.09	26.09	27.09	28.09	29.09	30.09
John	6		2​						2​		2​
Anna	2		2​						0​		0​
Mark	0		0​						0​		0​
Georg	6		2​						2​		2​
Ben	0		0​						0​		0​
Sophia	0		0​						0​		0​
Susan	6		2​						2​		2​

sheets I add (this sheets is unknown number at the beginning of the month) - each sheet will have different dates in row 1 (however some of the could be the same).

name	sum	2.09	8.09	10.09
John	3	p	p	p
Anna	1	p
Mark	0
Georg	3	p	p	p
Ben	0
Sophia	0
Susan	3	p	p	p

 

[jasonb75]

I'll try a couple of things and see if I can come up with anything, but in all honesty I think that you have too many unknown variables for this to work.