# Countifs problem

#### shjoaquin

##### New Member
I am not sure if I am using the correct formula, but I am trying to answer the following question.

I am trying to add coordinators on day shift (c\$2:c\$470,"d*") that work in the bay area (d\$2:d\$470,"b*") and were notified prior to their shift starting (h\$2:h\$470,"y*")...

Here is the forumla I am using:
=countifs(c\$2:c\$470,"d*",d\$2:d\$470,"b*",h\$2:h\$470,"y*")

When I calculate each coordinator individually for all shifts it works out, but the sums at the bottom by individual shift do not add up.

Thanks for any help,
Shawna

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Jonmo1

##### MrExcel MVP

Counts how many rows (between 2 and 470) that meet the following 3 criteria
1. Column C Begins with the letter d
2. Column D begins with the letter b
3. Column H begins witht he letter y

It works fine for me.

#### AusSteelMan

##### Board Regular
Hi,

Should it be "countif", not "countifs"

However, when I try that, I get an "Error in formula" with "D\$470" highlighted. Get the same error when I tried as a CSE formula.

I'll keep trying.

ASM

#### Jonmo1

##### MrExcel MVP
Hi,

Should it be "countif", not "countifs"

However, when I try that, I get an "Error in formula" with "D\$470" highlighted. Get the same error when I tried as a CSE formula.

I'll keep trying.

ASM

Countifs is an XL2007 function.
Allows you to count on multi criteria

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,Criteria3)

#### AusSteelMan

##### Board Regular

Thanks. Learnt another thing today!!

I am still on 2003, although I have heard talk of work changing to 2007.

Bye.

#### shjoaquin

##### New Member
I really cannot figure out the problem. all my formulas in the worksheet with two criteria are working fine, the ones with three work, no error, but the math does not add up. Anyone else have any suggestions to try and find out whats wrong?

For example: by each individual coordinator the math is right...but the sums at the bottom where I want them separated by shift and region are wrong: it's only counting about half of the rows it should be??

Hmmm?

#### barry houdini

##### MrExcel MVP

can you give an example. Which formulas give which results etc.

#### shjoaquin

##### New Member
I might have just figured it out...I have the shifts in column C labeled in groups of merged cells. Does this mean only the first cell recongnizes the word "days"

does anyone know a solution for this?

#### shjoaquin

##### New Member
I was able to fix the problem by labeling every cell in column C as the proper shift...now everything adds up....its not as pretty as being able to merge these cells, but it works....again if anyone knows a way around this please let me know...I love to learn new things.

Thanks for the responses and your time.

#### LxQ

##### Well-known Member
Here are some thoughts:
-add to the formula a criteria that if a cell is empty, it would look for the last cell with a value in it starting from C2 to the cell above the current one
-can you set the cells so that if they are the same as the cell above, then it wouldn't show, but the value would still be in the cell?

Replies
24
Views
382
Replies
2
Views
182
Replies
8
Views
98
Replies
1
Views
58
Replies
25
Views
1K

1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

### 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.

### Which adblocker are you using?

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

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