SUMPRODUCT active link formula

Unexpc

Active Member
Joined
Nov 12, 2020
Messages
496
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.
 
Upvote 0
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: 14
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Need a small, clear example (with row numbers and column letters) showing exactly what should and should not be included.
 
Upvote 0
the simple link formula (for example =Sheet!B3) like formula i upload for link, that is matter is count active cell
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top