Adding a CountIF to a formula that is already Countif

**Cmorgan** · 06-01-2011, 09:12 AM

Morning,

I'm using this formula:

=COUNTIF('Data Sheet'!L:L, "yes")/COUNTA('Data Sheet'!L2:L225)

Which works out what percentage of Column L on the Data Sheet has the value yes.

What I now need is to check another column first, it has been suggested a COUNTIF formula. I've tried playing around with it and can't seem to get it to work, probably as I'm not sure where the all ( and , need to go

I've ordered the Excel and Formulas for dummies books so hopefully I'll learn how to do it, rather than keep posting for formulas. Until it turns up any help with this formula would be appreciated.

So, a formula that:

before it uses =COUNTIF('Data Sheet'!L:L, "yes")/COUNTA('Data Sheet'!L2:L225) it will check column b on the data sheet to see if the value in A1 on the formulas sheet is present.

Thanks for any help you can give.

**NBVC** · 06-01-2011, 09:14 AM

Probably you can use Countifs...

e.g.

=COUNTIFS('Data Sheet'!B:B,$A$1,'Data Sheet'!L:L, "yes")/COUNTA('Data Sheet'!L2:L225)

you should probably be consistent with the ranges though... i.e either L:L or L2:L225 in both functions...

**Cmorgan** · 06-01-2011, 09:30 AM

Thanks for this:

=COUNTIFS('Data Sheet'!B:B,$A$1,'Data Sheet'!L:L, "yes")/COUNTA('Data Sheet'!L2:L225)

When I put it in, it 'works' but gives the wrong value. It could possibly be because it is looking for the value of A1 on the data sheet, rather than on the formula sheet. I tried to put in the 'reference' to the formula sheet, but it then doesn't work. I tried this:

=COUNTIFS('Data Sheet'!B:B,'Formulas'!$A$1,'Data Sheet'!L:L, "yes")/COUNTA('Data Sheet'!L2:L225)

Any ideas, please?

**Cmorgan** · 06-01-2011, 09:33 AM

Sorted, I realised one of the column numbers was wrong.

A big thanks for your help, I can now finally get this done

**NBVC** · 06-01-2011, 09:34 AM

How doesn't it work? Error or wrong count?

If A1 is on the same sheet you are entering the formula in, then you don't need to reference the sheet name, if it isn't then you do need to reference the sheet name.

Why do you have range L2:L225 in the COUNTA() function instead of L:L?