Something like COUNTIF but can take multiple ranges?

Hello,

I want to use the function COUNTIF through a spreadsheet but it won't allow me to use it over more than one range. The only way I can get it to work is if I retype the whole function for each range. There are gaps and and headers in the spreadsheet in which I don't want to include the function and that is causing problems with the cells that I do want to use it for.

Example of what works: =COUNTIF('Book1'!G4:G51, "Complete")
Example of what I want but does not work =COUNTIF('Book1'!G4:G51,G53:G70, "Complete")

Is there another function or would something like this require VBA code?

Thanks and I can provide more detail if needed.

In your example what might be in G52? Would there be a header value (in A52?) that would allow the cells you don't want counted to be identified?

The header value would be different in Column B where I am marking the tasks as R/C, R, O, or N/A. Column G would be the same through the spreadsheet.


There is a column(B) that indicates if the piece in column G is required or not. In column B there could be an R/C, R, an O, or it could be N/A. I would only want to include R/C and R.


In column G, there can be Not Started, In Progress, or Complete. I want to count if the item is marked Complete.

There reason I want multiple ranges is because the tasks are listed and some are marked, for example, R for G1:G10, then R for G12:G15. G11 could be marked O.

Could I just add an IF statement before the COUNTIF? Not sure how that would look..