+ Reply to Thread
Results 1 to 7 of 7

Counting cells that meet multiple single criteria as variable criteria

  1. #1
    Registered User
    Join Date
    06-24-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    Google Sheets
    Posts
    10

    Counting cells that meet multiple single criteria as variable criteria

    I am trying to count how many values meet multiple criteria (as in a standard countifs) but also meet the criteria of being greater than another value in their same row. If possible I would prefer to do this without a helper column (though I know a helper column can easily solve this).

    The code that I tried is this:
    Please Login or Register  to view this content.
    It's that last argument in the range which is tripping me up. The resulting output effectively ignored that last output (using Google Sheets) and the answer was the same as it would have been without it.

    Here is an example of what I'm working on:

    I've tried searching for other answers and could only find SUMPRODUCT, which does work but does not allow me to also apply the multiple single criteria I need from the COUNTIFS as well.

    Any suggestions most appreciated!
    Attached Files Attached Files
    Last edited by BillBasil; 01-28-2016 at 03:52 PM.

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,721

    Re: Counting cells that meet multiple single criteria as variable criteria

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,898

    Re: Counting cells that meet multiple single criteria as variable criteria

    A sumproducts solution should work, although it is hard for me to be sure without a spreadsheet (as Pepe states) on which to test. Try:
    Please Login or Register  to view this content.
    Also let me ask if any of the data in column C is equal to/less than the corresponding data in column B (if not then that should be why the results don't change).
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    06-24-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    Google Sheets
    Posts
    10

    Re: Counting cells that meet multiple single criteria as variable criteria

    Thank you, just added a sample!

  5. #5
    Registered User
    Join Date
    06-24-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    Google Sheets
    Posts
    10

    Re: Counting cells that meet multiple single criteria as variable criteria

    Thanks so much, that worked perfectly!

  6. #6
    Registered User
    Join Date
    06-24-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    Google Sheets
    Posts
    10

    Re: Counting cells that meet multiple single criteria as variable criteria

    Okay, so that solution worked great -- but here's another twist. What is I wanted to count values which were greater than others in the range by a set number.

    For example

    Please Login or Register  to view this content.
    Is there a way to do that with SUMPRODUCT or can that only be done with helper columns?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,898

    Re: Counting cells that meet multiple single criteria as variable criteria

    Try entering this as an array formula:
    Please Login or Register  to view this content.
    Array formulas are activated with the keystrokes Ctrl + Shift + Enter (not just enter).
    Let me know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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