+ Reply to Thread
Results 1 to 8 of 8

Countif or sumproduct

  1. #1
    Forum Contributor
    Join Date
    03-24-2014
    Location
    Lisboa, Portugal
    MS-Off Ver
    Office 365
    Posts
    174

    Countif or sumproduct

    Hi guys
    I think this is an easy one for you. I cannot get there...
    I have 2 columns to sum only in those lines they're diferent values.
    I did a comparation between 2 columns, and i want the sum of column G if the value is different on column H.
    I've attach a file
    Hope you understand that would be a great help for me.
    Tks in advance
    Pedro
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,502

    Re: Countif or sumproduct

    Try in F12:

    =SUMPRODUCT((F5:F10<>G5:G10)*F5:F10)

    G12:

    =SUMPRODUCT((F5:F10<>G5:G10)*G5:G10)

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Countif or sumproduct

    Try these two array formulas

    for column F

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


    For G

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


    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif or sumproduct

    Try

    =SUMPRODUCT(--($G5:$G10<>$H5:$H10),G5:G10)

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,107

    Re: Countif or sumproduct

    I'd go with:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: missed the second one:

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



    Regards, TMS
    Last edited by TMS; 11-09-2015 at 11:48 AM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Contributor
    Join Date
    03-24-2014
    Location
    Lisboa, Portugal
    MS-Off Ver
    Office 365
    Posts
    174

    Re: Countif or sumproduct

    Guys
    Many thanks for all your help
    Best regards
    Pedro

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countif or sumproduct

    You're welcome.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,107

    Re: Countif or sumproduct

    Ditto. You're welcome.

+ 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] I need help with sumproduct and countif
    By jayait2000 in forum Excel General
    Replies: 14
    Last Post: 06-08-2012, 01:48 PM
  2. Countif or Sumproduct?
    By Scotsman89 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-08-2011, 05:46 AM
  3. [SOLVED] SumProduct or CountIf
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] SumProduct or CountIf
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 06:05 AM
  5. SumProduct or CountIf
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] SumProduct or CountIf
    By Kim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. SUMPRODUCT or COUNTIF??
    By KirstieA in forum Excel General
    Replies: 1
    Last Post: 07-27-2005, 07:06 AM
  8. countif or sumproduct
    By cape in forum Excel General
    Replies: 1
    Last Post: 02-02-2005, 01:12 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