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]

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

this count Text SUM too
but for my problem i find a solution but still i need help you
i can use this formula =Count(Sheet!D3:D1048576) (i don't know why until know not think about that, i find out accidently )
just say, how can this formula count two data of same row just one thing?
data in same row of Column D,F and E,G
this formula i send in first post, i think this need !Sheet but i don't know how add it in this
=SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))+(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))-SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))*(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))

 

[Unexpc]

this count Text SUM too
but for my problem i find a solution but still i need help you
i can use this formula =Count(Sheet!D3:D1048576) (i don't know why until know not think about that, i find out accidently )
just say, how can this formula count two data of same row just one thing?
data in same row of Column D,F and E,G
this formula i send in first post, i think this need !Sheet but i don't know how add it in this
=SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))+(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))-SUMPRODUCT((ISNUMBER(D:D))*(NOT(ISFORMULA(D:D)))*(ISNUMBER(F:F))*(NOT(ISFORMULA(F:F))))

=SUMPRODUCT((ISNUMBER(Sheet!D:D))*(NOT(ISFORMULA(Sheet!D:D)))+(ISNUMBER(Sheet!F:F))*(NOT(ISFORMULA(Sheet!F:F))))-SUMPRODUCT((ISNUMBER(Sheet!D:D))*(NOT(ISFORMULA(Sheet!D:D)))*(ISNUMBER(Sheet!F:F))*(NOT(ISFORMULA(Sheet!F:F))))
this is right?

 

[jasonb75]

this count Text SUM too

No, it doesn't. It excludes text SUM.

i can use this formula =Count(Sheet!D3:D1048576) (i don't know why until know not think about that, i find out accidently )
just say, how can this formula count two data of same row just one thing?

You can't use that, the second part is not possible with COUNT.

Your attempt at using ISFORMULA will not work. There are no formulas in 'Sheet' for it to count.

If the formula in post 31 is not working then that is a problem with your data, not with the formula.

 

[Unexpc]

=SUMPRODUCT((ISNUMBER(Sheet!D:D))*(NOT(ISFORMULA(Sheet!D:D)))+(ISNUMBER(Sheet!F:F))*(NOT(ISFORMULA(Sheet!F:F))))-SUMPRODUCT((ISNUMBER(Sheet!D:D))*(NOT(ISFORMULA(Sheet!D:D)))*(ISNUMBER(Sheet!F:F))*(NOT(ISFORMULA(Sheet!F:F))))
this is right?

that is work for me, i prefer this function with own sheet (count in main sheet not another) but this work

 

[Unexpc]

No, it doesn't. It excludes text SUM.

but when i enter formula, show 3 thing count at start

You can't use that, the second part is not possible with COUNT.

that is skip count data that in same row

Your attempt at using ISFORMULA will not work. There are no formulas in 'Sheet' for it to count.

Sheet the name of sheet that linked data

 

[Unexpc]

please test last formula i send

 

[jasonb75]

but when i enter formula, show 3 thing count at start

Then there must be something else other than SUM which is to be excluded. The formula is based on the information provided, if the information is inaccurate or incomplete then the formula will be the same.

that is skip count data that in same row

The COUNT function can not skip entries, it is not designed to work that way.

Sheet the name of sheet that linked data

ISFORMULA counts the number of formulas in the range Sheet!D:D there are no formulas in Sheet!D:D to count so the result will always be FALSE (or TRUE when used with NOT). It will not count formulas in the Print sheet that are linked to Sheet!D:D

please test last formula i send

You said it works, so that is tested. My formula works for me so if it is not working for you then there are differences in the data that I can not see from the exampled provided which likely means that your formula will also give me different results.

 

[Unexpc]

You said it works, so that is tested. My formula works for me so if it is not working for you then there are differences in the data that I can not see from the exampled provided which likely means that your formula will also give me different results.

Yes, you say right i find another text in this sheet, i have two question
1.this is until work 10000 rows, can i change to like A:A and this work until last end excel rows?
2.how can i add exclude text in this formula?
i test my formula and work but your formula is better than me to use
generally, Thank you for giving your time and follow up

 

[jasonb75]

1.this is until work 10000 rows, can i change to like A:A and this work until last end excel rows?

Do you really need entire columns? Entire columns are a bad idea with most formulas. They are a very bad idea when volatile functions such as INDIRECT are involved. If you only have 10,000 rows of proper data then using the entire column is wasting 99% of the processing effort. With your sheet, I would expect a long delay every time you make a change to your data.

Also, using the entire column will include anything at the top of the sheet (rows 1:7) which was why one of the earlier formulas did not work correctly.

2.how can i add exclude text in this formula?

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

You can add as many exclusions as needed using the *(A8:A10000<>"TEXT")* method, remember that they must all start and end in the same rows to avoid problems.

 

[Unexpc]

Do you really need entire columns? Entire columns are a bad idea with most formulas. They are a very bad idea when volatile functions such as INDIRECT are involved. If you only have 10,000 rows of proper data then using the entire column is wasting 99% of the processing effort. With your sheet, I would expect a long delay every time you make a change to your data.

Also, using the entire column will include anything at the top of the sheet (rows 1:7) which was why one of the earlier formulas did not work correctly.

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

You can add as many exclusions as needed using the *(A8:A10000<>"TEXT")* method, remember that they must all start and end in the same rows to avoid problems.

ok, Thank you again