Results 1 to 2 of 2

Thread: Use SUMIFS on pivot table data

  1. #1
    Junior Member
    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    Use SUMIFS on pivot table data

    Hi,

    The following formula results in "Value" and I can't understand why. I have a simple pivot table summarizing meals by department number. I need to sum meals based on a range of dept numbers.
    # of meals in columns L and M of pivot table
    Dept #'s in column K of pivot table

    =SUMIFS(L5:M36,K5:K36,">="&O4,K5:K36,"<="&O5)

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Welcome to board.

    Use either of this

    =SUMPRODUCT(--(K5:K36>=O4),--(K5:K36<=O5),L5:L36+M5:M36)

    =SUM(IF(K5:K36>=O4,IF(K5:K15<=O5,L5:M36)))

    The second one is an array formula.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Change Pivot Table Data Source Using A Drop Down List
    By hanishgautam in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-05-2013, 09:33 AM
  2. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  3. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •