#### Unexpc

##### Active Member
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))))
=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)))),""))))

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### jasonb75

##### Well-known Member
i want count active cells of formula that linked to another sheet
It is not clear exactly what is required. There is only ever one active cell, which is the one that will change if you start typing.

Without supporting information, a formula like the one that you have provided means very little to anyone other than the person who wrote it.

#### Unexpc

##### Active Member
It is not clear exactly what is required. There is only ever one active cell, which is the one that will change if you start typing.

Without supporting information, a formula like the one that you have provided means very little to anyone other than the person who wrote it.
sorry, better say, when i copy data to specific sheet, in main sheet show that data, some cells show empty in main sheet while it fill with a formula, i want count only data that show in this range not empty
for example(that is a range of formula i send in upper post not number):

#### Attachments

• Screenshot 2021-04-04 013008.png
7.4 KB · Views: 6

#### jasonb75

##### Well-known Member
As your data is numeric, you only need a simple count.
Excel Formula:
``=COUNT(D:D)``
Will ignore the blank cells, you simply need to use the range of the existing formulas in it.

#### Unexpc

##### Active Member

As your data is numeric, you only need a simple count.
Excel Formula:
``=COUNT(D:D)``
Will ignore the blank cells, you simply need to use the range of the existing formulas in it.
your right but it skip two things, first all sum formula in that column and count two data in same row just one thing not two

#### Unexpc

##### Active Member
my problem with this is count number, if it count active cells data (not formula because it just count formula even show empty), the problem is solved

#### jasonb75

##### Well-known Member

Need a small, clear example (with row numbers and column letters) showing exactly what should and should not be included.

#### Unexpc

##### Active Member
the simple link formula (for example =Sheet!B3) like formula i upload for link, that is matter is count active cell

#### Unexpc

##### Active Member
Need a small, clear example (with row numbers and column letters) showing exactly what should and should not be included.

#### Unexpc

##### Active Member
Need a small, clear example (with row numbers and column letters) showing exactly what should and should not be included.
this is sheet have data
Workbook.xlsm
ABCDEFG
1TextTextTextTextText
2TextTextTextText
3212121121
42121212-21
5212121321
6212121434
72121215-76
821212165
92121217-2
102121218-8
1121212195
Sheet

this sheet that linked to sheet have data
Cell Formulas
RangeFormula
E3E3=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))))
H3H3=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))))
C4C4=INDEX((A8),1,1,1)
G4G4=IFERROR(IF(MOD(ROW()-6,34)<=1,"",LOOKUP(10^35,A:A)),"")
A8:A16A8=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:B17B8=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:C16C8=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:D16D8=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)))),""))))
E8E8=IF(AND(A8=""),"",SUM(C8:D8))
F8:F16F8=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:G16G8=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)))),""))))
H8H8=IF(AND(A8=""),"",SUM(F8:G8))
E9:E16E9=IF(AND(A9=""),"",SUM(C9:D9,E8))
H9:H16H9=IF(AND(A9=""),"",SUM(F9:G9,H8))
F17:G17,C17:D17C17=SUM(C8:C16)
Named Ranges
NameRefers ToCells
Print_Area=Print!\$A\$1:\$H\$40G4

Replies
6
Views
110
Replies
5
Views
87
Replies
6
Views
109
Replies
155
Views
2K
Replies
4
Views
92

### Forum statistics

1,130,029
Messages
5,639,623
Members
417,101
Latest member
amoverton2 ### 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.

### Which adblocker are you using?    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

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