sum visible cell only

[kelvin_9]

Hi All,

i'm writing below formula to calculate the sales in a column without duplicate order, but i was wondering how can this count visible cells only but not a whole column?

=SUM((FREQUENCY(IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(E$50=data!$Y$2:$Y$20000),IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(E$50=data!$Y$2:$Y$20000))>0)*1)-1

thanks for your guidance

 

[Eric W]

The easiest way is to add a column on your sheet, say column Z. Put this formula in Z2:Z20000:

=SUBTOTAL(103,A2)

This will return a 1 if the row is visible, 0 if it is not. Then you can add another condition in your formula:

. . .  *($Z$2:$Z$20000=1)* . . .

 

[kelvin_9]

The easiest way is to add a column on your sheet, say column Z. Put this formula in Z2:Z20000:

=SUBTOTAL(103,A2)

This will return a 1 if the row is visible, 0 if it is not. Then you can add another condition in your formula:

. . .  *($Z$2:$Z$20000=1)* . . .

thank you very much for you reply, Eric W

this with ctrl shift enter?

=SUM((FREQUENCY(IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(E$50=data!$Y$2:$Y$20000)*($AD$1:$AD$20000=1),IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(E$50=data!$Y$2:$Y$20000))>0)*1)-1

 

[Eric W]

That's correct.

 

[felixstraube]

You can also avoid the helper column like this:

=SUM((FREQUENCY(IFERROR(MATCH(Data!$A$2:$A$20000,Data!$A$2:$A$20000,0),0)*(INT(Data!$E$2:$E$20000)=$A52)*(E$50=Data!$Y$2:$Y$20000)*SUBTOTAL(103,OFFSET(Data!$A$1,ROW(Data!$A$2:$D$20000)-ROW(Data!$A$1),0,1)),IFERROR(MATCH(Data!$A$2:$A$20000,Data!$A$2:$A$20000,0),0)*(INT(Data!$E$2:$E$20000)=$A52)*(E$50=Data!$Y$2:$Y$20000)*SUBTOTAL(103,OFFSET(Data!$A$1,ROW(Data!$A$2:$A$20000)-ROW(Data!$A$1),0,1)))>0)*1)-1

 

[kelvin_9]

You can also avoid the helper column like this:

=SUM((FREQUENCY(IFERROR(MATCH(Data!$A$2:$A$20000,Data!$A$2:$A$20000,0),0)*(INT(Data!$E$2:$E$20000)=$A52)*(E$50=Data!$Y$2:$Y$20000)*SUBTOTAL(103,OFFSET(Data!$A$1,ROW(Data!$A$2:$D$20000)-ROW(Data!$A$1),0,1)),IFERROR(MATCH(Data!$A$2:$A$20000,Data!$A$2:$A$20000,0),0)*(INT(Data!$E$2:$E$20000)=$A52)*(E$50=Data!$Y$2:$Y$20000)*SUBTOTAL(103,OFFSET(Data!$A$1,ROW(Data!$A$2:$A$20000)-ROW(Data!$A$1),0,1)))>0)*1)-1

thank you very much for your reply, felixstraube

something wrong?
apology i cant use xl2bb to copy the data here and photo instead while exceed 3000 cells limtitation

after filter col D E Y, i copied col A to another new sheet and remove duplicate, i found the answer is 58

but the formula return 10 only

5.jpg

thank you very much

 

[felixstraube]

I'm trying with a some random data and it seems to work correctly.
Can you maybe share your file uploading it to a cloud storage? And I'll have a look.

 

[kelvin_9]

I'm trying with a some random data and it seems to work correctly.
Can you maybe share your file uploading it to a cloud storage? And I'll have a look.

thank you very much for your reply, felixstraube

this is my original workbook

i use my macro to run "136"
it return "10", but the correct answer should be "58"

also i run "simba"
it has somthing wrong which i am still thinking

book1.xlsm

Shared with Dropbox

www.dropbox.com

thank you very much

 

[kelvin_9]

I'm trying with a some random data and it seems to work correctly.
Can you maybe share your file uploading it to a cloud storage? And I'll have a look.

any update?

thank you very much

 

[felixstraube]

Hi Kelvin, sorry for the late response.
I think it is because you have data till row 38387, but your range goes from A2 to A20000. You should extend it to 38387.
Let me know it that fixes it.