Using IF with COUNTIF

[Rogojosh]

Hi there, some wonderful people have helped me here before, so asking politely once again.

So I have a number of people who must take certain actions when they receive an email. Column one is peoples initials, column two are the actions. Eg. I want to search for HW, and count how many occurrences of 'Notification in, await report'. I'm trying: =IF('[Email Report.xlsx]RD '!$G$2:$G$71*("HW"),COUNTIF('[Email Report.xlsx]RD '!$J$2:$J$73,"Notification in, awaiting report")) There's clearly errors there so any help would be very much appreciated thank you.

 

[GraH]

Perhaps try with countifs function. Similar to countif, but you can add more criteria or ifs.

 

[Rogojosh]

Perhaps try with countifs function. Similar to countif, but you can add more criteria or ifs.

Thanks GraH, I tried that but it counted how many times HW appeared, which I don't need. I just want to ask if it's HW, in the name column, then count how many whatever criteria ie actions. Also, in another tab that has no names, just actions, this works to count how many 'RD' are 'Open' =COUNTIF('[Email Report v1.xlsx]RD '!$I$2:$I$139,"Open") but if try that for another type of criteria, eg =COUNTIF('[Email Report v1.xlsx]RD '!$I$2:$I$139,"Notification In, Await Report") it isn't working. Perplexed. Thanks for your help.

 

[Joe4]

It looks like you are trying to use COUNTIF across workbooks. Are the workbooks that the formula is looking in open or closed?

Note that if they are closed, you have to use an Array formula (see: Using Countif on closed workbooks) or use a SUMPRODUCT formula (see: CountIfs to Sumproduct for Closed workbooks).

 

[Rogojosh]

It looks like you are trying to use COUNTIF across workbooks. Are the workbooks that the formula is looking in open or closed?

Note that if they are closed, you have to use an Array formula (see: Using Countif on closed workbooks) or use a SUMPRODUCT formula (see: CountIfs to Sumproduct for Closed workbooks).

Thanks, the workbooks are always open, when I update one, the others auto-populate. But I must say, that is good to know.