+ Reply to Thread
Results 1 to 6 of 6

Sumifs and countifs of values that are ON a List and NOT on the list

  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    29

    Sumifs and countifs of values that are ON a List and NOT on the list

    Hey everyone!

    Thanks for last time.

    Got another one here needing help.

    I have a 2 column table(A and B) and what I want is a quick Sumifs and Countifs of certain values that are on a separate list. And another sumifs/countifs for the values that are NOT on the list.

    Mind helping me with this one?

    I know there's a simpler way - to just do manually and filter things out. But I want the freedom to be able to change what's on the list and for things to give me the summary right away.

    Attached is the sample excel formula.

    Thanks in advanced!
    Attached Files Attached Files
    Last edited by zed369; 05-14-2015 at 10:16 PM. Reason: Reattached new sample file

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs and countifs of values that are ON a List and NOT on the list

    It's not real clear what you're wanting to do.

    How about updating the file with the results that you expect?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    29

    Re: Sumifs and countifs of values that are ON a List and NOT on the list

    Updated file with the results I wanted. Hope this helps!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs and countifs of values that are ON a List and NOT on the list

    Try these...

    J3:

    =COUNTA(E3:E7)

    J4:

    =SUMPRODUCT(SUMIF(A2:A27,E3:E7,B2:B27))

    J8:

    =SUMPRODUCT(--ISNA(MATCH(A2:A27,E3:E7,0)),--(B2:B27>5))

    J9:

    =SUMPRODUCT(--ISNA(MATCH(A2:A27,E3:E7,0)),--(B2:B27>5),B2:B27)

    J11:

    =SUMPRODUCT(--ISNA(MATCH(A2:A27,E3:E7,0)),--(B2:B27<5))

    J12:

    =SUMPRODUCT(--ISNA(MATCH(A2:A27,E3:E7,0)),--(B2:B27<5),B2:B27)

  5. #5
    Registered User
    Join Date
    03-26-2014
    Location
    Toronto
    MS-Off Ver
    MS Excel for Office 365
    Posts
    29

    Re: Sumifs and countifs of values that are ON a List and NOT on the list

    Worked! You are the best! I'm going to try to understand this now.

    Thanks so much!

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs and countifs of values that are ON a List and NOT on the list

    You're welcome. Thanks for the feedback!

+ 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. COUNTIFS & SUMIFS Not Grabbing Negative Values
    By boynejs in forum Excel General
    Replies: 4
    Last Post: 11-06-2014, 01:07 PM
  2. [SOLVED] Countifs From List.
    By izy0502 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-14-2014, 12:01 PM
  3. Adding list of values to another list of values (Inventory restocking)
    By eshell189 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2014, 04:34 PM
  4. [SOLVED] Sumifs all values not in a list
    By Hesh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2013, 10:47 AM
  5. Filter a long list by a shorter list of values, keeping repeated values
    By mrfloopa in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-15-2012, 09:50 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