Countifs problem

[shjoaquin]

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

 

[Jonmo1]

Hard to say from your description of your task, but here is what the formula does.

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]

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]

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]

Thanks. Learnt another thing today!!

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

Bye.

 

[shjoaquin]

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]

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

 

[shjoaquin]

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]

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]

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?