Help with Countif and Index Match

[CTMom]

Hi I need help with this formula (for some reason the add-in to load the actual spreadsheet is not working so apologies for copy and paste) - trying to count number of times the word occurs in each financial year and formula not working. First table has the data only. Year is just =year(Sale Date) so I can match year below.

Data Table
	Sale Date	Year	Sale Type
Holly	5/05/2018	2018	Online
Carter	3/09/2019	2019	Online
Serena	3/10/2019	2019	Referral
Parker	1/07/2020	2020	Channel
Sloane	2/07/2020	2020	Direct
Holly	4/07/2020	2020	Direct
Carter	1/01/2020	2020	Direct
Serena	5/08/2021	2021	Referral
Serena	6/04/2021	2021	Referral
Parker	4/04/2022	2022	Channel
Serena	5/08/2022	2022	Channel

Formula in B2 and rest of table is:

=COUNTIF(INDEX($D$14:$E$24,MATCH(B$1,$D$14:$D$24,0),),$A2)

	2018	2019	2020	2021	2022
Direct	-	-	-	-	-
Online	1.00	1.00	-	-	-
Referral	-	-	-	1.00	-
Channel	-	-	1.00	-	1.00

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try it like

=COUNTIFS($D$14:$D$24,B$1,$E$14:$E$24,$A2)

 

[CTMom]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try it like

=COUNTIFS($D$14:$D$24,B$1,$E$14:$E$24,$A2)

Thanks for responding so quickly! worked perfectly.

 

[Fluff]

Glad to help & thanks for the feedback.