+ Reply to Thread
Results 1 to 6 of 6

Show cells used in sumif

  1. #1
    Registered User
    Join Date
    08-02-2006
    Posts
    9

    Show cells used in sumif

    Hi

    I'm using the sumif statement to sort out my bankstatemet - i'm in the need of being able to see which cells are added together in the sumif.

    I have tried using F5 (Go-to special) but that just highligts the entire column i'm using as my range - any idea to how this is done.

    My formula is: =SUMIFS($C$2:$C$1852;$B$2:$B$1852;"Husleje*";$F$2:$F$1852;J8)

    /Jesper

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,303

    Re: Show cells used in sumif

    You can use Conditional formatting
    Select C2:C1852 & use this formula
    =AND(B2="Husleje*",F2=$J$8)

  3. #3
    Registered User
    Join Date
    08-02-2006
    Posts
    9

    Re: Show cells used in sumif

    Thanks - I'm quiet able to get it to work, but will give a go more.
    I have around 30 different sorting tags times 12 month so I was hoping for a simpler sollution.
    /Jesper

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,303

    Re: Show cells used in sumif

    Can you please supply a sample file.


    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    08-02-2006
    Posts
    9

    Re: Show cells used in sumif

    Hi

    I have made a sample with test data - the size is not a big as the actual sheet but the concept is the same.

    /Jesper
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,303

    Re: Show cells used in sumif

    Ok, thanks for the file.
    With Cf the formula should be
    =AND(ISNUMBER(SEARCH($G$3,B2)),$D2=$H$2)
    2 other options
    1) Filter the records with the autofilter
    2) a Macro if your happy with that.

    I've added the first 2 CF formulae & I've also changed the formula in H3
    Attached Files Attached Files

+ 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. Formula to show percentage of cells in each segment in custom formatted cells
    By newbie_Lau in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-03-2016, 01:21 PM
  2. Show individual values in a SumIf formula
    By jake14569 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2013, 07:00 PM
  3. [SOLVED] SUMIF calcualting zero but don't want zero to show
    By szp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2013, 05:07 PM
  4. Replies: 10
    Last Post: 02-24-2012, 08:21 AM
  5. Using sumif to show win loss when 1 team plays 4 others
    By Laurence1965 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-22-2011, 02:07 PM
  6. Replies: 1
    Last Post: 05-17-2011, 11:39 PM
  7. SUMIF and show blank if zero on timesheet
    By karstens in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2006, 04:48 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