Forum Discussion

MBMAdmin's avatar
MBMAdmin
Copper Contributor
Jul 03, 2023

I've got the SUMIFS blues

I posted the same discussion a couple days ago, but don't see it on here anymore. Thought I'd try again. I'm using Microsoft 365 Apps for Business version of Excel on a PC. 

Background: I'm making a spreadsheet that I use to track payments of patients for a specific date of service. It is set up to allow up to 5 payments for each date of service. Each payment section includes three pieces of data: Date, payment code, and amount. The code indicates how the payment was made, credit card, card on file, or prepaid. Each payment code carries a different merchant fee. I'm trying to come up with a formula that will total the merchant fees for each code over the entire table of five payment sections and any number of rows. Credit card fee is 2.6%+0.10 (Ex: $20 charge has $0.62 fee -- $20 x 0.026 = 0.52+0.10 cents=$0.62.), Card on File fee is 3.5%+0.35 cents, and Prepaid fee is 2.9%+0.30 cents.

The date columns are not particularly relevant at this time and would just make the whole formula more complicated, it seems to me. So what I'm looking for is a total amount of merchant fees paid for each of the above code categories.  Not: "INS" and "CO" have no merchant fees. Only the three codes above are at issue. Thanks for any help, even if it's to say it can't be done practically.

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 11, 2023

    MBMAdmin 

    =SUMPRODUCT(IF($B$3:$N$8=$G13,$C$3:$O$8*$H13+$I13))

    An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jul 11, 2023

    Hi MBMAdmin 

     

    I do not have a formula solution for you. But instead, I propose Power Query.

    In the attached file you find my solution. The list with transactions has been converted into a formatted table.

    Beside, there is a reference table with the transaction fees per code.

    Both tables are then imported into Power Query and transformed, so the individual fees can be calculated. The results are directly loaded from Power Query into a pivot table.

     

    All you have to do is maintain the table with the transaction fees and then do a refresh on the pivot table once there are new payments made.

     

     

    Kind regards,

    Martin

     

     

     

Resources