Using match and countifs

[macquhele]

Hi there. I have a spreadsheet with a data entry sheet where I log agent names, errors and a 'red' marker where appropriate for each error. I've used the countifs formula with no issue i.e =COUNTIFS('Sheet1'!A:A,"Macquhele,'Sheet1'!B:B,"Red").

However I have over 400 names meaning creating each countifs formula could take a while. So I created a second worksheet with the list of names and attempted to create a countifs formula that firstly matched each name on column A on sheet 2 to column A on sheet 1 then counting the number of occurances of 'red' errors on sheet 1 for that matched name but it doesn't work. Any help with this would be greatfully appreciated. Cheers

[Shareez Saleem]

Can you please upload a sample file with the expected results?

[AliGW]

Not easy to troubleshoot without seeing the workbook, and your statement, "but it doesn't work", tells us nothing.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

EDIT: This is incorrect:

=COUNTIFS('Sheet1'!A:A,"Macquhele,'Sheet1'!B:B,"Red")

It should be:

=COUNTIFS('Sheet1'!A:A,"Macquhele",'Sheet1'!B:B,"Red")

[Richard Buttrey]

IT would help if you upload the workbook, however I can see no reasons why if on sheet 2 you have the names in column A this won't work on sheet2

Please Login or Register  to view this content.

[macquhele]

Apologies i don't seem to have an upload link although working from my phone so think I need to log in via desktop. In the meantime will try out formula above

[macquhele]

Your formula worked thank you. I was clearly over thinking it. Cheers for your help