Help coming up with a Function to count number of cells containing text "yes"

**Rosadocc** · 04-25-2012, 09:55 AM

Hi everyone,

I am trying to figure out a function that will do the following:

I have six(6) sheets. The first five(5) represent day of the week (Monday, Tuesday...) and the last sheet has the week totals. In each sheet, cell I5 will have either "Yes" or "No" text that was entered by the user. On the last sheets I5, I am looking to total up the number of "Yes" responses from the other 5 sheets.

Please note, the I5 cells in sheets 1-5 have a drop down menu that allows the user to select "yes" or "no". I originally wanted to use the COUNTIF function to do this but it seems like having the drop down validation on the cells doesn't allow the COUNTIF function to count the number of "yes".

So for example the function would work like this: if the user selects "yes" on Monday and Wedndesday sheets, the last sheets I5 cell will have "2" in the cell.

**dilipandey** · 04-25-2012, 10:02 AM

Hi Rosadocc,

So for example the function would work like this: if the user selects "yes" on Monday and Wedndesday sheets, the last sheets I5 cell will have "2" in the cell.

Below formula will help you to get the count of "yes" where you need to replace the sheet names with your sheet names i.e., Monday, Tuesday etc.

=(Sheet3!A1="yes")+(Sheet2!A1="yes")+(Sheet1!A1="yes")

Regards,
DILIPandey

<click on below 'star' if this helps>

**Rosadocc** · 04-25-2012, 10:10 AM

I'm not sure if that is what I am looking for. Each sheet's I5 cell will have either a yes or a no value. On the last sheet I am simply looking for a function that will calculate how many "yes" values the other sheets had.

**dilipandey** · 04-25-2012, 10:22 AM

Hi Rosadocc,

This is what formula will do.. it will check each sheets (Monday, Tuesday etc..) for "yes' in I5 and will provide the result.. but yes, you need to modify the formula as I mentioned in my previous post... so the revised formula willl look like :-

=(Monday!I5="yes")+(Tuesday!I5="yes")+(wednesday!I5="yes").......

I have check this and formula is giving correct results.

Regards,
DILIPandey

<click on below 'star' if this helps>

**royUK** · 04-25-2012, 10:30 AM

Why don't you use one sheet?

**ChemistB** · 04-25-2012, 10:33 AM

Also, relative to your first post, it has nothing to do with dropdowns. CountIf doesn't work across multiple sheets like that unfortunately. dilipandey's solution will work for you.

**dilipandey** · 04-25-2012, 10:46 AM

Thanks chemistB for seconding me... cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**Rosadocc** · 04-25-2012, 11:54 AM

Yes I see I made a mistake on changing the name of my sheets. Your function works great thanks!. One more question if you could please help me out. Is there a way I can take this formula and add a function to leave that cell blank if all responses are "no"?

**ChemistB** · 04-25-2012, 12:01 PM

Unfortunately, with Excel 2003, there are no shortcuts, the formula would be

=IF((Monday!I5="yes")+(Tuesday!I5="yes")+(wednesday!I5="yes")....... =0,"",(Monday!I5="yes")+(Tuesday!I5="yes")+(wednesday!I5="yes")....... )

**Rosadocc** · 04-25-2012, 01:22 PM

I have Excel 2010. Would this make it any different? Thanks in advance.

**ChemistB** · 04-25-2012, 01:42 PM

Actually, in rethinking, since no error is generated when there are no "Yes's" there is no difference. You could format the cell(s) to not show 0
Format Cell> Number > Custom
Type in General;-General;
The zero is still there, you just can't see it in the cell (you will see it in the formula bar).
Does that work for you?

**Rosadocc** · 04-25-2012, 01:42 PM

Its okay, this formula is good enough. THANKS!

Help coming up with a Function to count number of cells containing text "yes"

LinkBack

Thread Tools

Rate This Thread

Display

Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Re: Help coming up with a Function to count number of cells containing text "yes"

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions