Countifs across a range

Hi

I'm fairly new to excel and have been teaching myself via online help.

I'm trying to figure out how to calculate a total across a large range and on two seperate tabs. I'm using Countifs and keep getting '#VALUE' with the following formula:

Below is a small example however the actual data sheets consists of 50 columns and over 300 rows

Untitled.png

I need to find the text 'Yes' in all columns and count it firstly and then ensure that it's only finding the 'Yes' answer for Store A.

The formulas I thought would work is countifs:

=COUNTIFS(C3:E3,"YES",B3:B8,"STORE A")

Any advice??? Would I need to used index match formulas instead?

Many thanks in advance for any assistance.

There are at least 2 ways you could do that

=SUMPRODUCT((C3:E3,="YES")*(B3:B8="STORE A"))

=COUNTIF(INDEX(C3:C8,MATCH("STORE A",B3:B8,0),0),"YES")

As you're trying to teach yourself, I'll leave you to figure out how each one works, but if there is anything you don't understand, please ask

Thanks, Jason. I found that the Index Match works if both the tables are on the same sheet however I get the same error value when I move the tables on two separate sheets on the same workbook. Also realised why initially the Index Match wasn't working for me (I was missing out one of the zero's on the formula near the end)

Can you post the formula that you're using with 2 sheets please, Vinnie?

There are a couple of things that could cause the error you're seeing but without seeing the formula and the layout of each sheet it's practically impossible to figure out the exact reason why it's not working.

Hi Jason.b75

Sorry for the late response. It was a user error on my side. The CountIf worked how I needed it to.

Thanks again for you help.