+ Reply to Thread
Results 1 to 6 of 6

Sumif single cell across multiple sheets to return blanks instead of 0

  1. #1
    Registered User
    Join Date
    12-07-2017
    Location
    NWA, Arkansas
    MS-Off Ver
    2016
    Posts
    5

    Sumif single cell across multiple sheets to return blanks instead of 0

    I'm new and really only joined because I need help with this problem. I have an above-layman understanding of Excel, having taught myself by researching online, but this has stumped me for over a week and at this point I have wasted too much energy getting nowhere and need help.

    I'm working on building an attendance database for a bereavement team that tracks throughout the year and sums each quarter. Excel had a template that was exactly what I was needing. I got my Pivot tables set up and I'm almost done but because the sheet that sums all sheets for an annual total returns a 0, the counts on my pivot table are counting each individual listed in the database instead of the individual non-zero cells in the column.

    I've read through several posts here and elsewhere saying to use the SUMPRODUCT(SUMIF(INDIRECT( formula but every time I've seen that used, it is summing a range of data on each sheet; I need to sum 1 specific cell on 4 sheets and have a blank cell instead of a zero.

    The formula I have now is =SUM('1st Q:4th Q'!C2).

    Thanks for any help!
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sumif single cell across multiple sheets to return blanks instead of 0

    you can try this one: =IF(SUM('1st Q:4th Q'!C2)=0,"",SUM('1st Q:4th Q'!C2))
    or un-tick in Display Option - Show a zero in cells that have zero value

  3. #3
    Registered User
    Join Date
    12-07-2017
    Location
    NWA, Arkansas
    MS-Off Ver
    2016
    Posts
    5

    Re: Sumif single cell across multiple sheets to return blanks instead of 0

    The formula did hide the zeros but the Pivot table is still counting cell in the column.

    So maybe this is more of a Pivot table question. Any suggestions, based on the spread sheet, on how to get the count to accurately reflect the data I'm wanting? I had a more stripped down version of this database where the was only one sheet and the count Pivot table worked then but now that I've broken out the data it's not working like I want it to.

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

    Re: Sumif single cell across multiple sheets to return blanks instead of 0

    On your Year-to Date Summary sheet enter formula in A2 and drag formula across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AlKey; 12-07-2017 at 09:05 PM.
    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

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Sumif single cell across multiple sheets to return blanks instead of 0

    I refreshed your PT and relationship disappeared

    anyway you can try with conditional formatting, if value = 0 set font color to white

    or

    Hide zero values in a PivotTable report

    Click the PivotTable report.

    On the Analyze tab, in the PivotTable group, click the arrow next to Options, and then click Options.

    Click the Layout & Format tab, and then do one or more of the following:

    Change error display Check the For error values show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

    Change empty cell display Check the For empty cells show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

    or, like i said above, un-tick this option:

    no0.jpg

    if you want blank instead of zero on all sheets - select all sheets first then untick this option
    Last edited by sandy666; 12-07-2017 at 09:44 PM.

  6. #6
    Registered User
    Join Date
    12-07-2017
    Location
    NWA, Arkansas
    MS-Off Ver
    2016
    Posts
    5

    Re: Sumif single cell across multiple sheets to return blanks instead of 0

    I was able to get the data counted how I wanted by using your formula and then going to the Pivot table and, instead of using "Count", I used "Count Numbers". I will mark this "SOLVED" as soon as I figure out how to.

+ 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. Replies: 3
    Last Post: 09-01-2016, 05:08 AM
  2. [SOLVED] Return multiple names into a single cell
    By Wikster7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-14-2016, 02:14 PM
  3. [SOLVED] vLookup multiple values separated by ";" in single cell / return result in single cell
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2015, 12:02 PM
  4. Return non blanks from multiple columns in multiple sheets
    By msj12345 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2015, 02:31 AM
  5. Replies: 1
    Last Post: 09-23-2014, 06:42 PM
  6. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  7. How to return multiple IF values in a single cell?
    By macmanjpc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2008, 08:54 AM

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