# Counting multiple cells with a condition in the middle.

#### happymouth

##### New Member
Here is my issue:

I need to be able to count all 5 columns on my sheet. I understand I can
do a countif. However, in the middle are two columns that needed to be counted as 1. I cannot figure out how to do this seperately, as the formulas constantly add up wrong.
I am sure this is a snap for someone here, it just isn't me.
Here's the basics:

Every person has 5 goals, however we count 2 of them as 1.
All the Ys below are calculated by a formula.

Janet gets

1. Y
2. Y
3. Y
4. Y
5. Y
6. which should equal 5. However, I need the 3rd and 4th Ys to count as 1, minusing 2 if one or both Ys are missing.
Examples:

Jennifer
1. Y
2. Y
3. this formula results in no Y, showing 0
4. Y
5. this formula results in no Y, showing 0
this should equal 2 for Jennifer

John

1. Y
2. Y
3. this formula results in no Y, showing 0
4. this formula results in no Y, showing 0
5. Y
this should equal 3 for John

Can anyone please help me? I could not attach a sheet but will give you any other info needed.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Gerald Higgins

##### Well-known Member
Assuming the results are in A1:A5, perhaps . . .

Code:
``=countif(a1:a5,"Y")-roundup(countif(a3:a4,"Y")/2,0)``

#### happymouth

##### New Member
The formula works terrific. Thank you so much, it will help me learn more as I study it. However, what I don't get is why it doesn't show up as
printable? It shows up in all blue, and then when formatted, still doesn't print??

***nevermind, it was my print area option...duh. Sorry!

Thanks again!!!!

Last edited:

Replies
1
Views
610
Replies
4
Views
438
Replies
3
Views
240
Replies
13
Views
399
Replies
1
Views
1K

1,191,199
Messages
5,985,233
Members
439,952
Latest member
djharter

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back