SUMPRODUCT active link formula

[Unexpc]

Hi guys
i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
=SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))+(ISNUMBER(G:G))*(NOT(ISFORMULA(G:G))))-SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))*(ISNUMBER(G:G))*(NOT(ISFORMULA(G:G))))
my link formula:
=IF(MOD(ROW()-6,34)=0,SUM($C$7:C7),IF(MOD(ROW()-7,34)=0,"بدهکار طلا",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!D"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!D"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!D"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!D"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))

 

[Unexpc]

Could you show an example with a wrong result, the same as the one in post 13 with the coloured numbers?

sorry for i bad say, this what i want:
i have 2 sheet, Sheet and Print
in Sheet main data that some cells fill and some cells empty, and cells of Sheet linked to Print cells
i want count cells in column C in Print that just show data except text and sum formula
a point that is column F related to column C, so i want when two data in same row of this two column, count one value instead of two value
and for column D and G like this two previous column
i hope i better say before

 

[jasonb75]

I think that I understand now, is this what you need? I've added some extra numbers into columns F and G to test the formula.

Note that the end of the data is identified by the word "SUM" in column A, not by the formulas in the other cells.

autoclose test.xlsm
	A	B	C	D	E	F	G	H
1
2
3			Count A		5	Count B		7
4			212121				212121
5
6
7	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT
8	212121	1	21		21		1	0
9	212121	2		-21	0			0
10	212121	3	21		21	1		0
11	212121	4	34		55	2	2	0
12	212121	5		-76	-21			0
13	212121	6	5		-16	3	3	0
14	212121	7		-2	-18			0
15	212121	8		-8	-26			0
16	212121	9	5		-21			0
17	SUM		86	-107		0	0
Sheet7

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(SIGN((C8:INDEX(C:C,MATCH("SUM",A:A,0)-1)<>"")+((F8:INDEX(F:F,MATCH("SUM",A:A,0)-1)<>""))))
H3	H3	=SUMPRODUCT(SIGN((D8:INDEX(D:D,MATCH("SUM",A:A,0)-1)<>"")+((G8:INDEX(G:G,MATCH("SUM",A:A,0)-1)<>""))))

 

[Unexpc]

I think that I understand now, is this what you need? I've added some extra numbers into columns F and G to test the formula.

Note that the end of the data is identified by the word "SUM" in column A, not by the formulas in the other cells.

autoclose test.xlsm
	A	B	C	D	E	F	G	H
1
2
3			Count A		5	Count B		7
4			212121				212121
5
6
7	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT
8	212121	1	21		21		1	0
9	212121	2		-21	0			0
10	212121	3	21		21	1		0
11	212121	4	34		55	2	2	0
12	212121	5		-76	-21			0
13	212121	6	5		-16	3	3	0
14	212121	7		-2	-18			0
15	212121	8		-8	-26			0
16	212121	9	5		-21			0
17	SUM		86	-107		0	0
Sheet7

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT(SIGN((C8:INDEX(C:C,MATCH("SUM",A:A,0)-1)<>"")+((F8:INDEX(F:F,MATCH("SUM",A:A,0)-1)<>""))))
H3	H3	=SUMPRODUCT(SIGN((D8:INDEX(D:D,MATCH("SUM",A:A,0)-1)<>"")+((G8:INDEX(G:G,MATCH("SUM",A:A,0)-1)<>""))))

yes, that is correct, that is i want but in next pages not count

 

[jasonb75]

but in next pages not count

I don't get what you mean. As I asked earlier, if it is not what you want, please post a small example where it is wrong and show me what it should be with highlighting.

 

[Unexpc]

i mean, this just count until cell that written SUM, if i right, i want count until excel cells end

 

[jasonb75]

So you have more that 1 SUM in column A?

 

[Unexpc]

So you have more that 1 SUM in column A?

Yes, may been several SUM but at the end of every page

 

[jasonb75]

As long as there is nothing else in column A to be excluded,
Count A

=SUM(COUNTIFS(A8:INDEX(A:A,MATCH("zzz",A:A)),"<>SUM",C8:INDEX(C:C,MATCH("zzz",A:A)),{"","<>","<>"},F8:INDEX(F:F,MATCH("zzz",A:A)),{"<>","<>",""}))

Count B

=SUM(COUNTIFS(A8:INDEX(A:A,MATCH("zzz",A:A)),"<>SUM",D8:INDEX(D:D,MATCH("zzz",A:A)),{"","<>","<>"},G8:INDEX(G:G,MATCH("zzz",A:A)),{"<>","<>",""}))

 

[Unexpc]

As long as there is nothing else in column A to be excluded,
Count A

=SUM(COUNTIFS(A8:INDEX(A:A,MATCH("zzz",A:A)),"<>SUM",C8:INDEX(C:C,MATCH("zzz",A:A)),{"","<>","<>"},F8:INDEX(F:F,MATCH("zzz",A:A)),{"<>","<>",""}))

Count B

=SUM(COUNTIFS(A8:INDEX(A:A,MATCH("zzz",A:A)),"<>SUM",D8:INDEX(D:D,MATCH("zzz",A:A)),{"","<>","<>"},G8:INDEX(G:G,MATCH("zzz",A:A)),{"<>","<>",""}))

it counts formula,
as far as i know, i say just count active cell, what thing i want, i want all data just show not empty (a example i send before) except SUM Value and other text and nothing, just linked cells show a data, thats it

 

[jasonb75]

i say just count active cell,

I've already pointed out in post 2 that what you said there means nothing, there is only ever one active cell, no more, no less.
What you want is not always possible, especially in the way that you want it. This means that we have to try other things to see if they work.

See if this one gives you the correct result. I've set it to look at 10,000 rows as other methods to make it more efficient by looking at the actual data range are clearly not working.

=SUMPRODUCT((A8:A10000<>"SUM")*(((C8:C10000<>"")+(F8:F10000<>""))>0))