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)))),""))))

 

[jasonb75]

You haven't specified what should be counted.

 

[Unexpc]

i want in columns c and f (is filled one of them) count 5 things in my example and columns d and g count 4 things
i not filled f and g but when fill this two column, for example in row 10 fill data in columns c and f, count one thing not two and this like for d and g too,

 

[Unexpc]

You haven't specified what should be counted.

Workbook.xlsm
	A	B	C	D	E	F	G	H
1
2
3			Count A		0	Count B		0
4			212121				212121
5
6
7	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT	TEXT
8	212121	1	21		21			0
9	212121	2		-21	0			0
10	212121	3	21		21			0
11	212121	4	34		55			0
12	212121	5		-76	-21			0
13	212121	6	5		-16			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
Print

Cell Formulas
Range	Formula
E3	E3	=SUMPRODUCT((ISNUMBER(C:C))*(NOT(ISFORMULA(C:C)))+(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))-SUMPRODUCT((ISNUMBER(C:C))*(NOT(ISFORMULA(C:C)))*(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))
H3	H3	=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))))
C4	C4	=INDEX((A8),1,1,1)
G4	G4	=IFERROR(IF(MOD(ROW()-6,34)<=1,"",LOOKUP(10^35,A:A)),"")
A8:A16	A8	=IF(MOD(ROW()-6,34)=0,"Text",IF(MOD(ROW()-7,34)=0,"Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!B"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!B"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
B8:B17	B8	=IF(MOD(ROW()-6,34)=0,"",IF(MOD(ROW()-7,34)=0,"Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!C"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!C"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!C"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!C"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
C8:C16	C8	=IF(MOD(ROW()-6,34)=0,SUM($C$7:C7),IF(MOD(ROW()-7,34)=0,"Text",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)))),""))))
D8:D16	D8	=IF(MOD(ROW()-6,34)=0,SUM($D$7:D7),IF(MOD(ROW()-7,34)=0,"Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!E"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!E"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!E"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!E"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
E8	E8	=IF(AND(A8=""),"",SUM(C8:D8))
F8:F16	F8	=IF(MOD(ROW()-6,34)=0,SUM($F$7:F7),IF(MOD(ROW()-7,34)=0,"Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!F"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!F"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!F"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!F"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
G8:G16	G8	=IF(MOD(ROW()-6,34)=0,SUM($G$7:G7),IF(MOD(ROW()-7,34)=0,"Text",IF(IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!G"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!G"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),"")=0,"",IFERROR(INDIRECT(LOOKUP(2,1/(("Sheet!G"&ROW()-6-INT((ROW()-6)/34)-INT((ROW()-7)/34))<>""),("Sheet!G"&ROW()-5-INT((ROW()-6)/34)-INT((ROW()-7)/34)))),""))))
H8	H8	=IF(AND(A8=""),"",SUM(F8:G8))
E9:E16	E9	=IF(AND(A9=""),"",SUM(C9:D9,E8))
H9:H16	H9	=IF(AND(A9=""),"",SUM(F9:G9,H8))
F17:G17,C17:D17	C17	=SUM(C8:C16)

Named Ranges
Name	Refers To	Cells
Print_Area	=Print!$A$1:$H$40	G4

 

[jasonb75]

C4: =SUBTOTAL(2,A8)
C17: =SUBTOTAL(2,C8:C16)
E3: =COUNT(C:C)

Same formulas for the other columns, edit as needed.

 

[Unexpc]

C4: =SUBTOTAL(2,A8)
C17: =SUBTOTAL(2,C8:C16)
E3: =COUNT(C:C)

Same formulas for the other columns, edit as needed.

the range is changable, anyway with first formula that changed that find out active cells? or find formula but just count that show data except text and sum formula in that range

 

[jasonb75]

Always start in row 8?
Always 1 row at the end do not count?

=COUNT(C8:INDEX(C:C,1048576))-1

 

[Unexpc]

Always start in row 8?
Always 1 row at the end do not count?

=COUNT(C8:INDEX(C:C,1048576))-1

that is correct, but when i insert show 1 thing counted before data is wirte, and another like formula i upload when write data in same row of Columns C and F, count one thing and for columns D and G too

 

[jasonb75]

I'm sorry, but I have no idea what you are trying to say.

 

[Unexpc]

I'm sorry, but I have no idea what you are trying to say.

ok thank you for give your time

 

[jasonb75]

but when i insert show 1 thing counted before data is wirte,

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