+ Reply to Thread
Results 1 to 9 of 9

Count cells with cretria to adjacent cells

  1. #1
    Forum Contributor Axmed.cm's Avatar
    Join Date
    02-05-2015
    Location
    Somalia
    MS-Off Ver
    2013
    Posts
    231

    Count cells with cretria to adjacent cells

    Dear All

    could anyone help me find how many cells matched the creteia based in adjacest cells as shown in attached spreadsheet and picture?

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files
    GUURE

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Count cells with cretria to adjacent cells

    Based on that sample, what would the answers be?
    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

  3. #3
    Forum Contributor Axmed.cm's Avatar
    Join Date
    02-05-2015
    Location
    Somalia
    MS-Off Ver
    2013
    Posts
    231

    Re: Count cells with cretria to adjacent cells

    Thank you FDibbins

    I want to put formulas in B10:B12 that reflects on desired results in C10:C12

    Thanks again
    Last edited by AliGW; 12-05-2019 at 02:19 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Count cells with cretria to adjacent cells

    Apologies if I was not clear, I meant, what would the actual answers here be?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Count cells with cretria to adjacent cells

    OK If I understand you correctly...
    equal pay =SUMPRODUCT(--($C$4:$C$8=$D$4:$D$8))
    Overpay=SUMPRODUCT(--($C$4:$C$8>$D$4:$D$8))
    Underpay=SUMPRODUCT(--($C$4:$C$8<$D$4:$D$8))

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,348

    Re: Count cells with cretria to adjacent cells

    The second and third questions are easy:

    =SUMPRODUCT(1*($C$4:$C$8<$D$4:$D$8))

    =SUMPRODUCT(1*($C$4:$C$8>$D$4:$D$8))

    The first is not clear. Do you want the number of people getting the same pay? If so, what happens if, for example, two are getting 40 and two 60 - what would the answer be?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Count cells with cretria to adjacent cells

    Ali, I think you got those 2 the wrong way round?
    overpaid = 1 (Ahmed)
    underpaid = 2 (last 2)

  8. #8
    Forum Contributor Axmed.cm's Avatar
    Join Date
    02-05-2015
    Location
    Somalia
    MS-Off Ver
    2013
    Posts
    231

    Re: Count cells with cretria to adjacent cells

    Dear All Thank you for your help.

    I have got how I wanted.

    The first is not clear. Do you want the number of people getting the same pay? If so, what happens if, for example, two are getting 40 and two 60 - what would the answer be?
    @Dear AliGW, I meant how many have got the exact amount (not under paid, nor over paid).

    I really appreciated all your support.

    Thanks again
    Ahmed

  9. #9
    Forum Contributor Axmed.cm's Avatar
    Join Date
    02-05-2015
    Location
    Somalia
    MS-Off Ver
    2013
    Posts
    231

    Re: Count cells with cretria to adjacent cells

    Hi Dear, Just one thing.. can I ignore in balank rows?

+ 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] How to count blank cells based on adjacent cells with names?
    By mutzie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2019, 08:33 PM
  2. Replies: 4
    Last Post: 02-11-2015, 09:02 AM
  3. A function that will count cells based on the value of the adjacent cells
    By grimston in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2014, 02:47 PM
  4. [SOLVED] Formula needed to only count total cells entered and not count adjacent text entered cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2013, 06:22 PM
  5. [SOLVED] Count in a range, where identical adjacent cells count as one instance.
    By the-algebraist in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-05-2013, 11:18 AM
  6. IF(Name=Name) then Count adjacent cells
    By neilpateluk in forum Excel General
    Replies: 0
    Last Post: 10-14-2009, 11:17 AM
  7. How to count several non-adjacent cells
    By RMF in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-23-2005, 12:10 PM

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