+ Reply to Thread
Results 1 to 10 of 10

Need help with creating formula to count cells based on 2 criterias

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    4

    Need help with creating formula to count cells based on 2 criterias

    Hello,
    I have created a spreadsheet that has 2 sheets. I need to create a formula in sheet B that will count the cell in sheet A based on the "if sheet A column F4:F511 has FT written in the cell" and "if sheet A column K4:K511 has a specific number in the cell such as "1450".

    I have tried several ways and have been unsuccessful. Please help ...

    Thanks
    Titleistseemore

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Need help with creating formula to count cells based on 2 criterias

    Try this...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by :) Sixthsense :); 02-11-2013 at 12:40 AM.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with creating formula to count cells based on 2 criterias

    Hi and welcome to the forum

    Without seeing a sample of what you are working with, its really hard to offer any suggestions. Your profile says you have 2003, so you cant use countufs(), however, perhaps try and add a helper column, combine the data from the 2 columns and base your count on that?

    If that doesnt work for you, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    02-11-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Need help with creating formula to count cells based on 2 criterias

    Here is a sample of the file I am working with.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with creating formula to count cells based on 2 criterias

    try this...
    =SUMPRODUCT(($C$1:$C$20="FT")*($D$1:$D$20=100))

  6. #6
    Registered User
    Join Date
    02-11-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Need help with creating formula to count cells based on 2 criterias

    That worked, Thanks to both sixthsense and FDibbens

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with creating formula to count cells based on 2 criterias

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

  8. #8
    Registered User
    Join Date
    02-11-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Need help with creating formula to count cells based on 2 criterias

    One more thing,
    When I fill the formula down, the cell numbers are changed, is there a way to fill the formula that will keep the cell numbers exactly the same?

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Need help with creating formula to count cells based on 2 criterias

    That is why FDibbins used $ symbol in post #5 to get rid of this issue

    Refer the below link for details.......
    http://www.cpearson.com/excel/relative.aspx

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with creating formula to count cells based on 2 criterias

    Thanks sixth

    $ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

    $A$1 absolutes the entire cell reference, it will not chance when copied down or across
    $A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
    A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1