INDEX, MATCH and COUNTIFS

[1953CAG]

Hi
I have seen an earlier thread on this, but it doesn't quite answer what I need. I have spreadsheet where I need to summarize values of specific criteria. I tried a PivotTable, but to add in all names means that I have to enter these in separate lines in the resourcing sheet, and that then interferes with any sorting of the data. I have tried doing something similar using COUNTIFS, but I can't seem to find something that will allow me to use the Sector as an initial filter (which would have been the slicer on a pivot table).

What I want is to count occurrences of specific products for each named PPM, but by sector. Once I have the formula right I can then replicate this for all other job roles.

Without the Sector complication this works quite well using COUNTIFS, but I've got completely stuck trying to add in the sector criteria.

The COUNTIFS formula is as follows:-

Please Login or Register  to view this content.

I've also attached a file, so that you can more easily see what I'm trying to do, I hope!

Thanks in advance if anyone has any suggestions

[samba_ravi]

b5=COUNTIFS(ResTbl[[#All],[PPM]],B$4,ResTbl[[#All],[Component]],$A5,ResTbl[[#All],[Sector]],$B$1)
is this you are looking for

[leelnich]

Hi all- @1953CAG, in your sample workbook, the value in B1 is not a valid sector. Make sure you select something you can actually find.

[1953CAG]

Hi nflsales

Many thanks, this is perfect. I've just added an IF statement around it so that I can show all results if the sector is left blank and it works perfectly. Hugely grateful, and I was so nearly there, I should have just been a bit more confident!
Thanks again, really appreciate the help.

[1953CAG]

Ah! I knew I'd miss something when I annonymised the spreadsheet. That sector was an original and should have been deleted from the cell.