formula of sum after filter on specific range

[arikh]

I have a data of days with details for each students. (S,F,L,V)
i would like to count each one for all the table.
more than i would like to sum the data after filter on specific range (such only first class on 22 on month )
Attached a file for example - will really glade to understand how to do it
Thank a lot

 

[Saba Sabaratnam]

Hi

I set up your data like the following.

Enter the following formula in B10 and copy it down.

=COUNTIFS($C$3:$G$6,A10)

And then enter the following formula in B20 and copy it down.

= SUMPRODUCT(--((INDEX($C$3:$G$6,,MATCH($B$17,$C$2:$G$2,0))=A20))*(--($A$3:$A$6=$B$19))*(--($B$3:$B$6=$B$18)))



Kind regards

Saba

 

[arikh]

Hi

I set up your data like the following.

View attachment 29252

Enter the following formula in B10 and copy it down.

=COUNTIFS($C$3:$G$6,A10)

And then enter the following formula in B20 and copy it down.

= SUMPRODUCT(--((INDEX($C$3:$G$6,,MATCH($B$17,$C$2:$G$2,0))=A20))*(--($A$3:$A$6=$B$19))*(--($B$3:$B$6=$B$18)))



Kind regards

Saba

Really thanks ...
But in case i want not to filter name or class (means i would like it to be only options) , what should i do ?
once i remove it , the results are 0 because it search null , i tried if but still return null
Thankss

 

[maabadi]

Try this:

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G
1
2	Name	Class	1	2	3	4	5
3	aaa	a	S	S	s	f	v
5	ccc	b	f	l	s	v	s
6	ddd	b	v	f	f	s	f
7
8		CountAll	CountVisible
9	s	7	6
10	l	4	1
11	f	5	5
12	v	4	3
Sheet2

Cell Formulas
Range	Formula
B9:B12	B9	=SUMPRODUCT(--($C$3:$G$6=$A9))
C9:C12	C9	=SUMPRODUCT(($C$3:$G$6=$A9)*(SUBTOTAL(103,OFFSET($C$3,ROW($C$3:$C$6)-MIN(ROW($C$3:$C$6)),0))))

 

[arikh]

Nice that visible option ...
I tried to use only one filter on the class and it failed , why ? (from the example above)
=SUMPRODUCT(--((INDEX($C$3:$G$6,,MATCH($B$18,$B$3:$B$6,0))=A20)))
(Once the data is not unique it not sum all of them ... )

 

[maabadi]

@arikh Are you test my Method? it really working well.

 

[arikh]

@arikh Are you test my Method? it really working well.

Yes it working well , but i have 2 sheets .
The first one that include all data
The second one is summarize all .
In the second one i just filter it by my self by 3 options :
1 - sum all data by filtered date + class .
2 - sum data by filtered date
3 - filter data by class ... in that one i have a problem - it's not working with this formula so will glade to undertand why ?
=SUMPRODUCT(--((INDEX($C$3:$G$6,,MATCH($B$18,$B$3:$B$6,0))=A20))) or maybe u have something more clever
Thanks a lot