Using Countif with another functions

I am trying to write a report using the COUNTIF function but in order to accomplish what the function needs to do, I need to use it with another function. Here is what I am trying to do:

I have a worksheet that is my data table and a worksheet that is my report.
I have a attached a visual to make this more clear. I am trying to count in the report the number of occurrences in the data table for 1A, 1B, etc for all rows that have Region # 0691 as 0691 is the region # for Chicago. There is more than 1 "region" that I need to do this for. I tried to use an IF statement and then embed a COUNTIF statement into that but it did not work as it returned a count of all cells that had 1A, not just the ones with Region # 0691. If anyone has any suggestions please let me know. I am trying to do this without using hidden cells or adding more worksheets. Thanks in advance for the help.

try this array:
=SUM(IF(range_of_status_code=A8,IF(range_of_Region#=A18,1,0),0))

cell A8 contains status code and cell A18 contains Region# as shown in image.
put appropriate ranges to above formula and press Ctrl+Shift+Enter (pressing only Enter will not work as this is any array formula)

hope this would serve your purpose.

Originally Posted by garungaha1

I am trying to write a report using the COUNTIF function but in order to accomplish what the function needs to do, I need to use it with another function. Here is what I am trying to do:

I have a worksheet that is my data table and a worksheet that is my report.
I have a attached a visual to make this more clear. I am trying to count in the report the number of occurrences in the data table for 1A, 1B, etc for all rows that have Region # 0691 as 0691 is the region # for Chicago. There is more than 1 "region" that I need to do this for. I tried to use an IF statement and then embed a COUNTIF statement into that but it did not work as it returned a count of all cells that had 1A, not just the ones with Region # 0691. If anyone has any suggestions please let me know. I am trying to do this without using hidden cells or adding more worksheets. Thanks in advance for the help.

Try this formula in B8 copied down

=SUMPRODUCT(--(status_range=A8),--(region_range=A$18))

status_range and region_range should be the same size and not whole columns