1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help with Formula please.

Discussion in 'Business Applications' started by Jacojs, Oct 30, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Jacojs

    Jacojs Thread Starter

    Joined:
    Oct 30, 2011
    Messages:
    4
    Hi.

    I can't get this formula to work.Countifs(H7:HL7,"FR")

    As it is it works fine but when adding a second criteria range that searches for "FR" as well I get a #value returned.

    It's a simple little leave planner but I am a noob so any help will be greatly appreciated.

    The spreadsheet is attached.

    Thanks
    Jaco.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    No problem I can see:

    I put the formula in yellow cell and it counts 2 FR
     

    Attached Files:

  3. Jacojs

    Jacojs Thread Starter

    Joined:
    Oct 30, 2011
    Messages:
    4
    Hi.

    Thanks for the reply but I will try to explain better.

    The formula that is working is currently in C81 and it reads =countifs(H7:AL7,"FR") This is just for the first employee and for January (row H7 to AL7)

    When I change the formula for the same person to include February the formula reads
    =countifs(H7:AL7,"FR",D13:AE13,"FR") however I dont get a value returned. This needs to be done up to December. Obviously If I can get it working for one person and one criteria the rest won't be a problem.

    If anything is unclear or you need more info please let me know.

    Again thanks for the help.
    Jaco.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I just put this in for the syntax, you will of course have to change it to cover the necessary fields.
    My suggestion, and well there ra emany more since its'hadn work to create new sheetw for every year, but thtá besides the point.
    I have a very simple idea and i'll out in the sheet I have here which will make it easier.

    One thing, where do you want the total's displayed?
    Another sheet? if you can tell me that it'll help me picture waht you want.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I put in two solutions, all will take initial work, but once there it's almost maiteance free
    Option 1: In colums AN through AX (I only used AN3, AO3 and AP3 y put in FR L S
    in the columns below the countifs(the actual row) AN7=IF(AN$3<>"",COUNTIFS(C7:AM7,AN$3),"")
    What is does is count the occurrence of row 3 in the row.
    Below I add all the rows in AN for Jaco for Familty Resp, All the rows in AO for jaco, etc.
    I filled in Jaco and Louis
    Option 2: I used in Matthew fro January and February, but you'll have to do that for the rest.

    Option 1 is more dynamic if you get a new type of leave you just add it in Row 3 AQ3 = V and you fill a V in Jaco i'tll add up right away.

    I hope my explanation is not too vague and that I've been able to explain myself.

    I think a macro will be much handier, but that will take a little VBA knowledg.

    Let me know
     

    Attached Files:

  6. Jacojs

    Jacojs Thread Starter

    Joined:
    Oct 30, 2011
    Messages:
    4
    Hi Keebellah.

    Thank you very much!! I am using option 2, not that there is anything wrong with the first option but option 2 is simpler for me, it's a lot more work but hey it'll keep me busy :)

    Just a question, why put the $ before the column and row? I found that even with it omitted the formula still works. Example $H7:$AL7 and H7:AL7 both give me the same return.

    Jaco.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The $ forces the reference to H
    copy the formula one column to the right and you'll see what I mean
    $H7:$AM7lets say H23=$H$7:$AM$7 and H24 = H7:AM7
    now copy both formulas one cell to the right and one row down
    I24=$H$7:$AM$7
    I25=I8:AO8
     
  8. Jacojs

    Jacojs Thread Starter

    Joined:
    Oct 30, 2011
    Messages:
    4
    Aah I see.

    Thanks again for your help.

    This thread can be closed.

    Jaco.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Don't forget to 'push' the "Mark Solved" button.
    Happy planning :)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1024663

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice