1

I'm struggling to construct a formula that will take a column of prices, and return the sum of those values only if another column contains a unique string, and during a certain time period and with a specified type.

The data contains client names, dates, transaction types, and transaction amounts. I have been able to get the number of unique values in a given timeframe that meets my criteria with this equation:

=SUM(--(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0)))))),ROW(Table13[Client])-ROW(C$20)+1)>0))

A12 is the month I'm summing for, C20 is where the data starts.

For A12 (October 2016), I get a unique count of 33. What I need is the sum of those 33 rows from the column: table13[contract/offer amount]

I attempted this formula, but returned inaccurate results:
=SUM(IF(Table13[Contract/Offer Date] <=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client] <>"",IF(Table13[Client]=Table13[Client],MATCH(Table13[Client],Table13[Client],0))),Table13[Contract/Offer Amount]))))

The client data does contain some empty cells, so correcting for that is important.

I know the above formula is wrong because the Contract/offer amounts are in the "Value if False" position of the formula, but this is the closest I have gotten.

Any help would be much appreciated, and let me know if you need any more details/clarification. Completely stuck.

enter image description here

UPDATE:


By returning to the initial sum,if,frequency equation, I was able to return "almost" the correct responses. The Unique count looks like this: =SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),1))

By substituting the offer amounts in place of the final "1", I am returning almost correct results.

=SUM(IF(FREQUENCY(IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),IF(Table13[Contract/Offer Date]>=A12,IF(Table13[Type of Transaction]="Offer",IF(Table13[Client]<>"",MATCH(Table13[Client],Table13[Client],0))))),ROW(Table13[Client])-ROW(C$21)+1),Table13[Contract/Offer Amount]))


What's unclear is why this is only partially working. I am imagining the results of the frequency formula constructing an array of 1s and 0s which are being multipled by the offer amounts. Is that correct?

8
  • Where is your example data?
    – DavidPostill
    Commented Feb 8, 2017 at 21:37
  • Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.
    – DavidPostill
    Commented Feb 8, 2017 at 21:37
  • Ah, I didn't get alerts of these comments - sorry for the delay. Here is a sample CSV screenshot. Hope this helps, let me know thanks
    – Joe MV
    Commented Feb 8, 2017 at 22:20
  • "unique values" - how do you want to manage multiple offers from the same client in a month? Calculate with only the first offer? Average? Commented Feb 8, 2017 at 22:35
  • Honestly, either will work. For simplicity-sake First appearance is fine. but average is probably more accurate? Thanks
    – Joe MV
    Commented Feb 8, 2017 at 22:38

1 Answer 1

0

ORIGINAL

So, I'm going to answer this question:

construct a formula that will take a column of prices, and return the sum of those values only if another column contains a unique string, and during a certain time period and with a specified type

Assuming the following:

  • "Client" you want is in A1 (I used client3)
  • "Start of time period" is in B1 (11/20/2016)
  • "End of time period" is in C1 (11/30/2016)
  • "Type" is in D1 (Offer)

Then, this formula:

=SUMPRODUCT(--(Table13[Client]=A1)*--(Table13[Contract/Offer Date]>=B1)*--(Table13[Contract/Offer Date]<=C1)*--(Table13[Type of Transaction]=D1)*Table13[Contract/Offer Amount])

Will return $402,000.


UPDATE

Attempting to breakdown this formula:

=SUM(IF(
  FREQUENCY(
    IF(Table13[Contract/Offer Date]<=EOMONTH(A12,0),
      IF(Table13[Contract/Offer Date]>=A12,
        IF(Table13[Type of Transaction]="Offer",
          IF(Table13[Client]<>"",
            MATCH(Table13[Client],Table13[Client],0)
      )))),
    ROW(Table13[Client])-ROW(C$21)+1),
  Table13[Contract/Offer Amount]
))

It's going to find the frequency of all the clients that match the criteria (in a certain month and only offers). Two problems here.

  1. FREQUENCY is going to additionally return the frequency of all clients unaccounted for. This is going to give you an extra index when you try to SUM with the "Amount".
  2. If you fixed problem #1, you're going to be summing the first "Amount" from that client (which is most likely outside the conditional date range).
6
  • I really appreciate your input on this problem, however the issue is that I'm not actually trying to produce a sum by an Individual Client. I want the sum of all unique clients for a specific time period. Let's suppose I have 50 offers in a given month, but only 25 of those are unique clients. So some clients have multiple offers. I need to know what the Sum of those 25 offers. The formula you offered only provides a sum for each client, not the sum of all unique clients.
    – Joe MV
    Commented Feb 14, 2017 at 21:24
  • Ok, we're getting warmer, but I'm still not grasping this concept of "unique clients". You want the sum of [the first offer from each client]?
    – UndeadBob
    Commented Feb 14, 2017 at 21:42
  • I re-read the update on your question and tried to make the second formula work. When you originally calculated the SUM of the FREQUENCY it worked fine. But you cannot multiply each value by the output of FREQUENCY by Table13[Contract/Offer Amount] because it returns one additional "bin" which will always result in "#N/A". See support.office.com/en-us/article/…
    – UndeadBob
    Commented Feb 15, 2017 at 14:15
  • Okay, I think I understand why the formula is inaccurate based on your explanation of the additional bin in the Frequency array. It still seems as though this problem should have a solution. Perhaps the formula I'm using isn't the right approach. As far as the "unique clients" concept, basically I'm trying to project avg. deal size. Clients often put in multiple offers, however they only end up with 1 contract. I'm trying to project an average transaction value, so in order to do that, I need to know how many "unique" clients put in offers and how much those offers were worth.
    – Joe MV
    Commented Feb 15, 2017 at 16:39
  • So, a "unique client" is simply one you specify? If so, my original answer is correct. Just put a list of all the clients in A1:Ax and copy my formula down to row x. That would give you the SUM of the offers for each client. Then, similarly calculate the number of offers with COUNTIFS to get an average of the offers from each (unique) client. You can reuse your EOMONTH to simplify my "start" and "end". If you want to calculate a sorted list of all clients (without duplicates) for this, I can post again...
    – UndeadBob
    Commented Feb 15, 2017 at 19:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .