Ask Experts Questions for FREE Help !
Ask
    suspectx0's Avatar
    suspectx0 Posts: 2, Reputation: 1
    New Member
     
    #1

    Sep 14, 2008, 07:59 PM
    Countif, if, and a mind boggling excel sheet
    All right, I am a slight novice at excel. I feel like I know a lot about it but the subject I'm trying to figure out is troubling me.
    I have an excel worksheet that in collum a I will have dates. And collum be I will have a drop down box that will allow you to click yes or no. so what I want is to be able to somehow show how many times someone says yes in a certain month.
    Ex: in collumn a I have 3 months worth of dates (november, January, February,i.e. 1/1/08,with some dates missing ). Then in collumn be I have a few yes's and 8 no's. And I want to be able to show in another part of the sheet how many times someone says yes in the month of January. I have been looking around the internet at nested ifs and arrays but I can't completely figure out how to use them in my cercumstances. As of right now I have my spreadsheet split up and just am using countif command and having the range a dedicated range (b2-b80) for one month then b81-b100) for a nother. I was wondering if there was a way so that if I put any date in any place on collumn A it would recognize it and know to add that date if it was a yes
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Sep 14, 2008, 10:02 PM
    Simple enough, but it uses the SUMPRODUCT to get the effect you want. You are basically multiplying the number of times ONE TRUTH is found in Column A by a SECOND TRUTH found in COLUMN B.

    First, you have to know how to tell what month is in each cell in Column A. That function is MONTH(reference).

    For instance, if the date in cell A2 is 1/4/2008, then the formula =MONTH(A2) would respond 1... 1 means January. 2 means February... etc.

    Since you now can test the month, you can do a test to find only cells that return a value of 1 (January).

    Next, you're going to find only the ones that are both 1 (January) and "yes" in the adjacent B column. Here's the initial formula... it's weird:

    =SUMPRODUCT((MONTH(A2)=1)*(B2="yes"))

    This will work because I made it true and only did one row. Now let's expand it to:

    =SUMPRODUCT((MONTH(A2:A4)=1)*(B2:B4="yes"))

    See the picture below. I put that formula into cell E2 and after filling in several entries, it works swimmingly.

    Lastly, you wanted to be able to put in as many dates as you want and check for every month's YES count. The easiest way to make the that happen is to use LABELS.

    To use a label highlight as much of the A column as you think you'll ever use... perhaps 100 cells. Notice the picture again, where you see "C2" in a white box above the A column, that's the NAME field. Type in the name DATES and press Enter.

    Now highlight the same number of cells in the B column, then name that group VOTES.

    Now you can replace the formula in cell E2 with:

    =SUMPRODUCT((MONTH(Dates)=1)*(Votes="yes"))

    Notice the A2:A4 was replaced with the word DATES? That's because it is now usable as a reference to the named range of cells called DATES. Nifty! It also makes the formula easier to read, you can look at it and tell what's going on. Names are great that way.

    Anyway, to get the count for February, just change the 1 to a 2. March would be 3.

    See the attached file example and look at the finished formulas in E2 F2 G2. You should get the idea.

    Don't jump to the end. Do what I've done one step at a time so you understand each segment of the problem. You don't even have to do the LABEL thing at all, I do it because it makes for clean looking formulas.
    Attached Images
     
    Attached Files
  1. File Type: xls Dates.xls (14.0 KB, 201 views)

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to connect one excel sheet to other excel sheet [ 28 Answers ]

How to connect one excel sheet to other excel sheet.

Connect two different sheet in Excel 2007 [ 6 Answers ]

I am using Excel 2007. I want to put the calculation part or the detail figure in one sheet. And I want to put the relating pi chart or some other graphical representation on some other sheet. How I would relate these two sheets so that when I make any changes in the figure in the first sheet...

Mind boggling philiosophy [ 3 Answers ]

Hey, this is my first philosophy essay and it has wrecked my head.? I know, I shouldn't have taken the subject. Ok this essay is extremely late and I have only done 301 words out of 1500 and I'm very stuck. :( OK here goes "why are the distinctions between knowledge and belif so important to...

Tick Sheet for Excel [ 0 Answers ]

I am creating a "Table of Measurements" for a moving company in Excel, and they require that in the "# of pieces" cells that a number "1" or a similar character be placed in the cell rather than a "2" for two pieces or a "3" for three pieces, etc. So if there are 4 pieces, the cell would have...


View more questions Search