Countifs problem

shjoaquin

New Member
Joined
Nov 16, 2009
Messages
5
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
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Sep 4, 2009
Messages
208
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Sep 4, 2009
Messages
208

ADVERTISEMENT

Thanks. Learnt another thing today!!

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

Bye.
 

shjoaquin

New Member
Joined
Nov 16, 2009
Messages
5
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
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

shjoaquin

New Member
Joined
Nov 16, 2009
Messages
5
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
Joined
Nov 16, 2009
Messages
5
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
Joined
Feb 9, 2006
Messages
619
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top