SUMPRODUCT active link formula

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows
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)))),""))))
 

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
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows
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
    Screenshot 2021-04-04 013008.png
    7.4 KB · Views: 6

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows
the simple link formula (for example =Sheet!B3) like formula i upload for link, that is matter is count active cell
 

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
314
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top