sorting and counting

Greetings everyone....
Would like to seek help and assistance in solving a problem of sorting and counting.

I have two columns (severity and supported group). severity column contains three 3 different types(major, warning, and critical). Supported group column have many groups.

I tried the below formula but didn't work. does anyone had any idea how it works

=COUNTIF('AllAlerts01-07'!A1:A1547,"Major")*('AllAlerts01-07'!B:B,"BSD*")
=SUMPRODUCT(('AllAlerts01-07'!A:A="Warning")*(('AllAlerts01-07'!B:B="BSD*")))

please check the attachment. thanks in advance.

this is the table in a separate sheet

Support group Critical Major Warning All Alerts
BSD



severity supported group
Critical BSD_Business Intelligence Group_BI
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Major BSD_ERP Support
Critical BSD_ERP Support
Critical BSD_ERP Support
Critical BSD_ERP Support
Critical BSD_ERP Support
Critical BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP Support
Warning BSD_ERP portal
Warning BSD_ERP portal
Major BSD_ERP portal
Major BSD_ERP portal
Major BSD_ERP portal
Major BSD_ERP portal

Hello, Massar, Welcome to he forum!

Try this, D9 copy to F9,

=SUMPRODUCT(--(LEFT('AllAlerts01-07'!$B$2:$B$34,LEN($C9))=$C9),--('AllAlerts01-07'!$A$2:$A$34=D$8))

and use a simple SUM in G9, =SUM(D9:F9)

If the all support group contains "cod/", try this,

G10, copy down across.

=SUMPRODUCT(--(LEFT('AllAlerts01-07'!$B$2:$B$34,3)=SUBSTITUTE(LOWER($F10),"cod/","")),--('AllAlerts01-07'!$A$2:$A$34=G$9))