+ Reply to Thread
Results 1 to 3 of 3

Countifs that compares two ranges

  1. #1
    Registered User
    Join Date
    12-14-2018
    Location
    Illinois
    MS-Off Ver
    2017
    Posts
    1

    Countifs that compares two ranges

    I'm trying to infer the number of phone lines that have been upgraded on an account based on the lease values entered into two seperate tables. If the line is for a phone, the new lease value doesn't match the old lease value, and the new lease value is not $0, then it can be considered an upgrade.

    C1:C15 tells us if the current line is a phone, M1:M15 tells us if that line is still a phone on the new plan, the current lease values are in F1:F15, and the new lease values are in P1:P15.

    Line 1 is an upgrade if C1="Phone AND M1="Phone" AND F1<>P1 AND P1>0.
    Line 2 is an upgrade if C2="Phone AND M2="Phone" AND F2<>P2 AND P2>0.
    etc...

    How many lines upgraded? My formula below is still counting the lines with identical lease costs on the two plans.

    =COUNTIFS(C1:C15,"Phone",M1:M15,"Phone",F1:F15,"<>" & P1,P1:P15,">0")

  2. #2
    Registered User
    Join Date
    06-21-2017
    Location
    Missouri, USA
    MS-Off Ver
    2007 through 2019
    Posts
    71

    Re: Countifs that compares two ranges

    The problem is in this part "<>" & P1
    You're comparing all the lines to the p1 value instead of say f14 to p14. A quick way might be to do a countifs on every row and then sum them.
    COUNTIFS(C1,"Phone",M1,"Phone",F1,"<>" & P1,P1,">0")

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Countifs that compares two ranges

    You can try this:
    Please Login or Register  to view this content.
    Quang PT

+ 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] COUNTIFS within Date Ranges
    By Jamie_QHSE in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-03-2017, 02:59 AM
  2. Countifs with date ranges
    By ljbrown in forum Excel General
    Replies: 2
    Last Post: 05-17-2017, 12:48 PM
  3. COUNTIFS between two ranges
    By Blackhawks in forum Excel General
    Replies: 8
    Last Post: 03-11-2015, 03:57 PM
  4. [SOLVED] countifs and date ranges
    By esaban in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2014, 06:49 PM
  5. [SOLVED] Using COUNTIFS with date ranges
    By Harrytheb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2013, 12:17 PM
  6. Using COUNTIFS with dynamic ranges?
    By Kagemucha in forum Excel General
    Replies: 5
    Last Post: 07-01-2007, 11:25 AM
  7. formula that compares different ranges
    By sir Lancelot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2005, 04:20 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