index and Countif

[jjayden]

I am looking to create a formula that will provide me with the count for the below 4 spaces based on the number in column 7 after matching the company name from column A
So is i am looking to populate cell b2 by matching the value in A2 with $F:$F then count the number that match the Urgency 4 and the same for C2 that matches the Ugency of 3 and so on

let me know if you can help

	A	B	C	D	E	F	G
1​	Company name	4​	3​	2​	1​	Company	Urgency
2​	Company 1					Company 1	3-Medium
3​	Company 2					Company 1	3-Medium
4​						Company 2	2-High
5​						Company 2	2-High
6​						Company 2	4-Low
7​						Company 1	3-Medium
8​						Company 1	2-High

 

[Anthony47]

Try, in B2

=SUMPRODUCT(--($F$1:$F$100=$A2),--(LEFT($G$1:$G$100,1)=LEFT(B$1,1)))

Copy B2 to the right; then copy B2:E2 down


D:\DDownloads\[MULTI_C30923.xlsm]Foglio3

 

[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’)

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Company name	4	3	2	1	Company	Urgency
2	Company 1	0	3	1	0	Company 1	3-Medium
3	Company 2	1	0	2	0	Company 1	3-Medium
4						Company 2	2-High
5						Company 2	2-High
6						Company 2	4-Low
7						Company 1	3-Medium
8						Company 1	2-High
Sheet5

Cell Formulas
Range	Formula
B2:E3	B2	=COUNTIFS($F:$F,$A2,$G:$G,B$1&"*")