COUNTIF and SUMPRODUCT with AutoFilter possibility

[sinasolar]

hi guys,

first of all I am new in VBA!

i have this worksheet as an example

	A	B	C	D	E	F	G
1	person	goods	price	%	$ paid	countif!	1/countif
2	X	A	100	30%	=D2*C2	=COUNTIF(B:B,B2:B2)	=1/F2
3	Y	A	100	20%	=D3*C3	=COUNTIF(B:B,B3:B3)	=1/F3
4	Z	A	100	10%	=D4*C4	=COUNTIF(B:B,B4:B4)	=1/F4
5	X	B	80	50%	=D5*C5	=COUNTIF(B:B,B5:B5)	=1/F5
6	Y	B	80	40%	=D6*C6	=COUNTIF(B:B,B6:B6)	=1/F6
7	Z	C	70	20%	=D7*C7	=COUNTIF(B:B,B7:B7)	=1/F7
8	X	D	90	70%	=D8*C8	=COUNTIF(B:B,B8:B8)	=1/F8
9				=SUMPRODUCT(C2:C8,D2:D8)	=SUBTOTAL(109,E2:E8)		=SUBTOTAL(109,G2:G8)

<tbody>
</tbody>

I wanted to count types of goods and i wanted to calculate how much money has spent, so this works correctly, but when I filter for example person "X" cell G9 will show 1.833 instead of 3 kinds of goods and the SUMPRODUCT will still calculate hidden rows.
i have found a way in other sites by defining name-ranges but it won't work for me because i am upgrading my original worksheets daily and new rows are added everyday.

i tried to write a simple VBA code for SUMPRODUCT (wish i could access the exact code because I'm not at my office)

here is the algorithm: (I'm not writing the exact code. this is what I remember. the original one is working correctly except the red lines)

function Sumpro (range1 as range, range2 as range)
dim rows1 rows2 n r col1 col2 as integer
sumpro=0
rows1=range1.cells.count
rows2=range2.cells.count
col1=range1.column
col2=range2.column
r=range1.row
if rows1=rows2 then
​

​

for n=1 to rows1
if activeworksheets.rows(n+r-1).hidden=false
sumpro=sumpro+application.worksheetfunction.index(range1,n,1)*application.worksheetfunction.index(range2,n,1)
end if​

next n
​

endif
end function

​

how can i write this
Does AutoFilter "Hide" the rows? (the property will be "hidden"?)

thanx

​

 

[LxQ]

Do you want to add extra columns? I put in this formula in J2:
=+SUBTOTAL(109,C2)/SUBTOTAL(109,C2)
and copied it down

this will be 1 if the row is showing, or 0 if the row is hidden

Then I modified the formula in F2
=COUNTIFS(B$2:B$8,B2, J$2:J$8,1)
and copied it down

 

[Aladin Akyurek]

Not very clear... Is this:

=SUMPRODUCT(C2:C8,D2:D8,SUBTOTAL(102,OFFSET(C2,ROW(C2:C8)-ROW(C2),0,,1)))

what you want?

 

[Marcelo Branco]

Not sure if i understand what you need.

If you want to count unique goods maybe something like this

	A​	B​	C​	D​	E​
1​	person​	goods​	price​	%​	$ paid​
2​	X​	A​	100​	30%​	30​
3​	Y​	A​	100​	20%​	20​
4​	Z​	A​	100​	10%​	10​
5​	X​	B​	80​	50%​	40​
6​	Y​	B​	80​	40%​	32​
7​	Z​	C​	70​	20%​	14​
8​	X​	D​	90​	70%​	63​
9​
10​		goods (unique)​			total​
11​		4​			209​

<tbody>
</tbody>

Array formula in B11
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2:B8,ROW(B2:B8)-ROW(B2),0,1)),MATCH(B2:B8,B2:B8,0)),ROW(B2:B8)-ROW(B2)+1),1))
Confirmed with Ctrl+Shift+Enter

Formula in D11
=SUBTOTAL(9,E2:E8)

After filter (person = x)

	A​	B​	C​	D​	E​
1​	person​	goods​	price​	%​	$ paid​
2​	X​	A​	100​	30%​	30​
5​	X​	B​	80​	50%​	40​
8​	X​	D​	90​	70%​	63​
9​
10​		goods (unique)​			total​
11​		3​			133​

<tbody>
</tbody>

Hope this helps

M.

 

[sinasolar]

Thanks alot @LxQ
Thanks alot @Aladin Akyurek
And Thanks to Marcello Branco

all three posts are helped me.

 

[Aladin Akyurek]

Thanks alot @LxQ
Thanks alot @Aladin Akyurek
And Thanks to Marcello Branco

all three posts are helped me.

That's great. Which guess fits what you had in mind?

 

[sinasolar]

That's great. Which guess fits what you had in mind?

about sumproduct definitely yours,

about countif both LxQ & marcello's

LxQ' method was cunning :d and Marcello's seems professional

this morning i wrote both sumproduct and countif in VBA, before i see these.
i will post it tomorrow

 

[sinasolar]

for Countif:
Function COUNTIT(range1 As Range, range2 As Range)
Dim rows1, rows2, col1, col2, r, n As Integer
COUNTIT = 0
rows1 = range1.Cells.Count
rows2 = range2.Cells.Count
If rows1 = 1 Then
r = range2.row
For n = 1 To rows2
If Not Rows(n + r - 1).Hidden Then
If Application.WorksheetFunction.Index(range2, n, 1).Value = Application.WorksheetFunction.Index(range1, 1, 1).Value Then
COUNTIT = COUNTIT + 1
End If
End If
Next n
End If
If rows2 = 1 Then
r = range1.row
For n = 1 To rows1
If Not Rows(n + r - 1).Hidden Then
If Application.WorksheetFunction.Index(range1, n, 1).Value = Application.WorksheetFunction.Index(range2, 1, 1).Value Then
COUNTIT = COUNTIT + 1
End If
End If
Next n
End If
If rows1 <> 1 And rows2 <> 1 Then
COUNTIT = 0
End If
End Function


for sumproduct:
Function SUMPRO(range1 As Range, range2 As Range)
Dim rows1, rows2, col1, col2, r, n As Integer
Dim v1, v2 As Double
SUMPRO = 0
r = range1.row
rows1 = range1.Cells.Count
rows2 = range2.Cells.Count
If rows1 = rows2 Then
For n = 1 To rows1
If Not Rows(n + r - 1).Hidden Then
If Application.WorksheetFunction.Index(range1, n, 1).Value = "" Then
v1 = 0
Else: v1 = Application.WorksheetFunction.Index(range1, n, 1).Value
End If
If Application.WorksheetFunction.Index(range2, n, 1).Value = "" Then
v2 = 0
Else: v2 = Application.WorksheetFunction.Index(range2, n, 1).Value
End If
SUMPRO = SUMPRO + v1 * v2
End If
Next n
End If
End Function