Forum Discussion

cpeterson's avatar
cpeterson
Copper Contributor
Apr 03, 2019

Formula/Function Formatting Question

I need help figuring out the best way to set up this table I've been working on. I track credit card payments for customers that we hold payment info on file for at my company. I have each customer s...
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 03, 2019

cpeterson , if source data for each customer is separated, you may transform such data to Excel tables (or, as variant, use dynamic named ranges). Each of such tables is named as customer name. In summary sheet you also have customer name which you may use with INDIRECT within SUMIFS to identify that customer table/range.

  • cpeterson's avatar
    cpeterson
    Copper Contributor
    Apr 03, 2019

    SergeiBaklan upon some more investigation on my part, I figured out what I want is a formula that takes place of the sum_range & criteria_range values within the SUMIFS formula. Here is a mockup of the spreadsheet I'm working with.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 03, 2019

      cpeterson , with your naming convention that could be like

      =SUMIFS(
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[[Amount ]]"),
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[Date]"), ">=" & $D$3,
   INDIRECT(SUBSTITUTE([@[Customer Name]], CHAR(32),)&"Table" & "[Date]"), "<=" & $E$3)

      Please see in first table attached

Resources