Counta, Index&Match? really stuck

[Mike_CS]

Hi everybody. My first ever post.

Would anybody know how to return a counta possibly using index and match? Or is there a better way.

I have a table to record when people are on leave.

The column headers are Grade / Team, Name, Staff Number, then the rest are dates in DD/MM/YYYY format and vertical to keep columns less wide (as it runs from Jun17 to March18).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Grade / Team
[/TD]
[TD]Name
[/TD]
[TD]Staff No
[/TD]
[TD]12/6/17
[/TD]
[TD]13/6/17 etc
[/TD]
[/TR]
[TR]
[TD]Grade 6
[/TD]
[TD]JOHN
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]AL
[/TD]
[/TR]
[TR]
[TD]Grade 7
[/TD]
[TD]PAUL
[/TD]
[TD]2
[/TD]
[TD]AL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grade 7
[/TD]
[TD]GEORGE
[/TD]
[TD]3
[/TD]
[TD]FL
[/TD]
[TD]FL
[/TD]
[/TR]
[TR]
[TD]TEAM1
[/TD]
[TD]RINGO
[/TD]
[TD]4
[/TD]
[TD]OFF
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEAM1
[/TD]
[TD]PETE
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEAM2
[/TD]
[TD]BRIAN
[/TD]
[TD]6
[/TD]
[TD]PRIV
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I would like is a subtotal of entries against each grade on a given date so if I was to choose another day, the answers below would also change.

I have a nested list of dates in a dropdown list and the list of Grade / Team entries e.g. Grade 7, Grade 6 (either could be 10 rows as 10 people are Grade 6).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]dropdown list with 1/6/17, 2/6/17 etc.[/TD]
[/TR]
[TR]
[TD]Grade 6[/TD]
[TD]hopefully the total number of entries on the selected date for grade 6 staff[/TD]
[/TR]
[TR]
[TD]Grade7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so if I choose 1/6/17 the grade 6 row would say 3 for example, grade 7 says nil but if I change the date to 6/6/17, grade 6 now shows 0, grade 7 shows 2 etc.

is this even possible? I am an absolute beginner but have been teaching myself what I need as I go but I cant work this out.

I know V and Hlookups are out as you'll only get the first occurance but in my head I can picture a SUM/COUNTIF/INDEX&MATCH hybrid that could change the world.

Apologies for rabbiting on. this is my first actual post so go easy on me ha.

All the best

Mike_CS

 

[wideboydixon]

Are you just counting non-empty cells? If so then perhaps something like this:

Book1
	A	B	C	D	E
1	Grade/Team	Name	Staff No	12/06/2017	13/06/2017
2	Grade 6	JOHN	1		AL
3	Grade 7	PAUL	2	AL
4	Grade 7	GEORGE	3	FL	FL
5	Team 1	RINGO	4	OFF
6	Team 1	PETE	5
7	Team 2	BRIAN	6	PRIV
8
9
10	Date	12/06/2017
11	Grade 6	0
12	Grade 7	2
13	Team 1	1
14	Team 2	1
Monkeys

Cell Formulas
Range	Formula
B11	=SUMPRODUCT(($A$2:$A$7=$A11)*(OFFSET($A$2:$A$7,0,MATCH($B$10,$1:$1,0)-1)<>""))

WBD

 

[Mike_CS]

Are you just counting non-empty cells? If so then perhaps something like this:


[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11
[/TH]
[TD="align: left"]=SUMPRODUCT(($A$2:$A$7=$A11)*(OFFSET($A$2:$A$7,0,MATCH($B$10,$1:$1,0)-1)<>""))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

Yeah Wideboy you're spot on. Just need non-blanks. I have recreated your example and it works spot on but when I translate it for mine it just displays 0.

My new formula is =SUMPRODUCT(($A$5:$A$59=KS12)*(OFFSET($A$5:$A$59,0,MATCH($KT$11,$E$3:$KR$3,0)-1)<>""))

Update as I was typing. I tinkered with the formula a little and it now works. I assume when the range isn't small you have to remove the finer details. This is the formula that seems to be working for me now:
=SUMPRODUCT(($A:$A=KS12)*(OFFSET($A:$A,0,MATCH($KT$11,$3:$3,0)-1)<>""))

Thanks very much Wideboy. I was absolutely miles away here but you showed me that I was totally thinking of the wrong sum.

If I'm ever in Sheffield i'll by you a pint.

Thanks - Mike_CS

 

[wideboydixon]

Hey Mike,

Glad you got it working. Bradfield Brewery Farmer's Blonde please ...

WBD