Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Multiple sumifs formula in a cell

    I created a worksheet which has a code NLC and pull in a number from another work sheet. I want to subtract the NLC amount from the TODAYS REFUNDED TODAY and PRIOR DAYS RECEIPTS REFUNDED TODAY.

    The TODAYS REFUNDED TODAY and PRIOR DAYS RECEIPTS REFUNDED TODAY sections may have different codes like the BMA or RME so the formula has to be flexible enough to pick up the sum of these two section and place in the A RECEIPTS section.

    In the same worksheet I produced a A RECEIPTS section to show what the NLC should look like (highlighted yellow).

    I tried SUMIFS but that doesn't appear to be the way to go.

    Thanks
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    If you're looking in only the four blocks, maybe this is what you're trying to do:

    In C3, I put: =SUMPRODUCT((B3=$B$18:$B$22)*$C$18:$C$22)+SUMPRODU CT((B3=$F$18:$F$22)*$G$18:$G$22)+SUMPRODUCT((B3=$B $32:$B$39)*$C$32:$C$39)

    and filled that down through C10.

    Is that it?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand correctly, sumif should work.
    In C32:
    =SUMIF(B$3:B$10,B32,C$3:C$10)-SUMIF($B$18:$B$22,B32,$C$18:$C$22)-SUMIF($F$18:$F$22,B32,$G$18:$G$22)
    Copy down the column

    In G32:
    =SUMIF(F$3:F$10,F32,G$3:G$10)-SUMIF($B$18:$B$22,F32,$C$18:$C$22)-SUMIF($F$18:$F$22,F32,$G$18:$G$22)
    Copy down the column

    Steve

Posting Permissions

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