Help with COUNTIFS for a relative newbie

[dharker]

Hi I need to count the number of elements in two different columns and would like to use countifs. If I use countif with the required individual criteria, I get valid results, when I combine them using COUNTIFS then I get a #VALUE! and no result. One of the columns contains business function e.g. accounts, finance etc. The other is status e.g. completed, in progress etc. The complication I think is that the business function can be empty, and this is where I'm having issues. If I COUNTIF($P1:$P320,"") I get a result, If I COUNTIF($D1:$D320,"In Progress") I get a result. If I COUNTIFS($P1:$P320,"",$D1:$D320,"In Progress") I get #VALUE!. Can anyone help me please? Much appreciated, Dave

 

[sh1pley]

Your code should work with the above. I've replicated in a spreadsheet and as long as both ranges are the same (go from cell 1 to 320 in your case) then it should be fine. Only when I changed one of the ranges did I get a #Value error

 

[dharker]

Your code should work with the above. I've replicated in a spreadsheet and as long as both ranges are the same (go from cell 1 to 320 in your case) then it should be fine. Only when I changed one of the ranges did I get a #Value error

You're brilliant! I'm so sorry to have wasted folks time with this - the range was 1 out. Corrected, the formula works and I'm getting what I need! Thank You!