+ Reply to Thread
Results 1 to 12 of 12

Countif with multiple criteria and calculations

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Countif with multiple criteria and calculations

    Hello,

    I'm trying to use Countifs to compare each cell to multiple columns, sometimes involving calculations within those comparisons. I'm trying to find out who earns less than a specified differential per year of service. I've attached my Excel file and will explain it here:

    For example, the first entry is Alpha A (job title and grade) and earns $3750 with 11 years of service. The second entry is also an Alpha A but earns $3700 with only 5 years of service. As specified in H2, each year of service should bring a salary differential of 1%. So the first Alpha A should be earning at least 6% (1% X 6 years) more than the second Alpha A, but actually earns only 1.35% ($50) more. So the count in F2 should be "1" meaning there is one person who earns more than that individual based on the rule (1% per years of service).

    Here's what my formula looks like in theory (obviously doesn't work because I'm subtracting with ranges): countifs($A:$A,$A2,$B:$B,$B2,$D:$D>$D2,$C:$C>($C2/($H$2*($E2-$E:$E))))

    I'm trying to avoid array formulas because my file is very large. I'm not sure if Countif is even the best formula to be using.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Countif with multiple criteria and calculations

    Are you comparing only among Alphas or among all users?

    And avoiding arrays is not question of choice.
    You won't be able to avoid them except multiple collumns (maybe, didn't yet worked on solution).

  3. #3
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Countif with multiple criteria and calculations

    Among all users; that is, Alpha As with Alpha As, Alpha Bs with Alpha Bs, Beta As with Beta As, etc.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Countif with multiple criteria and calculations

    Try this (array entered):

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by zbor; 05-13-2016 at 06:24 PM.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Countif with multiple criteria and calculations

    No, wait...
    This one:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Normal enter but it's still array formula.

  6. #6
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Countif with multiple criteria and calculations

    Thanks - for the second Alpha, I get '2' but the result should be 0, since that individual was hired last and earns the least.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Countif with multiple criteria and calculations

    Reverse <

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    [/FORMULA]

  8. #8
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Countif with multiple criteria and calculations

    Thanks - I'm still getting unusual results. For example, Gamma D hired in 2015 has a result of "1" even though this individual was hired last and has the highest salary.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Countif with multiple criteria and calculations

    I worked this out as follows. Assume the 1% increase per year. Solve the compound interest formula A = P(1+r/n)^nt for A (Amount) which, given n=1, yields P = A/(1+r)^t That yields the initial salary for the employees. The formula to do that is in column F and reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Column G then calculates the amount of annual increase using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula in column H then ranks employees with similar job titles and grades based on annual increase using the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The ranking ends up showing the number of employees with the same job tile and grade that are getting a larger annual increase, which if I understand correctly is what you want to determine.
    Let me know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Countif with multiple criteria and calculations

    I didn't consider rank.
    And also didn't figure out that need to find initial paycheck.
    I thought that was initial salary.

    So this will return same result in one cell (per JeteMc example):

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-16-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Countif with multiple criteria and calculations

    JeteMc: This worked marvelously! Thank you so much.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Countif with multiple criteria and calculations

    You're Welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] multiple criteria for calculations
    By tsh1586 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 05-06-2014, 05:22 PM
  2. Countif, Multiple Criteria, Multiple Sheets, Excel 2003
    By shyammankoo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 06:50 AM
  3. [SOLVED] trying to do a "countif" with multiple ranges and multiple criteria. Countif, Sumproduct?
    By completelyhis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2013, 06:12 PM
  4. Trying to COUNTIF when multiple criteria are met on multiple column data set
    By TGCRequiem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2011, 12:58 AM
  5. using countif with multiple corresponding criteria
    By macmandan1 in forum Excel General
    Replies: 5
    Last Post: 02-15-2010, 09:51 PM
  6. Countif w/ Multiple Criteria-How do I use countif
    By Patrick_KC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2005, 05:05 PM

Tags for this Thread

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