# Thread: Count if and (XP)

1. ## Count if and (XP)

It's me again. See attached - now she wants a formula in A5 on the "Totals" sheet that looks at Column B, Sheet 1 AND IF a 9 is found, AND IF what's in the same row as the 9 in Column H is a "yes", then count the number of 9's that have a "yes" . ?????? Clear as mud - or not.

It's the best I can do - help please.

thanks

2. ## Re: Count if and (XP)

Try this.
I made an ARRAY formula. confirm with ctrl-shift-enter not enter

I added some "yes" to sheet1 just for demo.
I also took the liberty of changing the Totals! A2:E2 to the numbers 9 - 12 (I custom formatted to 0" Grade" to keep the display as 9 Grade - 12 Grade). Putting the numbers made being able to copy fomulas across the columns

Steve

3. ## Re: Count if and (XP)

Thanks Steve - perfect. I am intersted to know why the "UPPER" in the formula. I thought all that function did was convert text to upper case and I don't understand why that part is needed in this formula. Please enlighten me - I'm always anxious to learn.

Linda

4. ## Re: Count if and (XP)

Try using =SUM((Sheet1!\$C\$3:\$C\$1492=A\$2)*(Sheet1!\$H\$3:\$H\$149 2="YES")) instead of
=COUNT(IF((UPPER(Sheet1!\$H\$3:\$H\$1492)="YES")*(Shee t1!\$C\$3:\$C\$1492=A\$2),1)) in cell A5 Sheet!Total

The end result is the same value and just another way of looking at things.

5. ## Re: Count if and (XP)

Cool - still would like to know what UPPER did in the first formula you wrote, if you think I can grasp it (ya never know!).

Regardless - thank you very much for your help - it was a huge help.

Linda

6. ## Re: Count if and (XP)

Hi Linda,
The UPPER function is used to remove any case-sensitivity, since UPPER("yes") = UPPER("Yes") = UPPER("yES") = UPPER("YES") etc so it will count any variation of Yes. In fact this should not be necessary in this case, but it's better to be safe than sorry!
Hope that helps.

7. ## Re: Count if and (XP)

Yes, This was my reasoning. You asked for "YES" but i tend to be lazy and would type "yes" instead which doesn't equal "YES" unless you UPPER it first. Just somethnig to make it a little more user-friendly without adding too much code.

Steve

8. ## Re: Count if and (XP)

And excellent reasoning it was. Thanks for the explanation - anything new I can learn makes me happy.

Linda

9. ## Re: Count if and (XP)

Hi Rory - thank you for the explanation - I'll never forget it now, that's for sure. It's been a pleasure.

Linda

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•