Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Copilot for Microsoft 365 Tech Accelerator

Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)

Microsoft Tech Community

SOLVED
Home
#
Sum of incentive amount based on the cell values

- Home
- Microsoft 365
- Excel
- Re: Sum of incentive amount based on the cell values

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 12:04 AM

Hey there, I m trying to calculate the total sum of incentive amount in term of models. I am attaching a screenshot where you easily understand things.

In table 1 , These are the models no with their incentive, table 2 is my sales data sheet.

Salesman name & model will be in table 2.

Now i want to calculate the total sum of incentive amount based on model.

Please help me to get rid out of this.

I tried sumproduct with sumifs formula but getting error

thankyou in advance...

10 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 12:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 12:34 AM

Hey, thanks for quick reply but that not what i want, actually i want sum of total of incentive amount in term of name

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 01:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 02:40 AM

Thank you for your response sir, For now, i got my answer but didn't understand how this formula work

best response confirmed by
ishaqib* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 03:23 AM

SolutionWe may do step by step

In first step we return the matrix where each column corresponds to each model in Table1. Row indicates if such model is in related row of Table2 in Models. We use double dash "--" to convert TRUE and FALSE to 1 and 0 accordingly.

On second step we MMULT above matrix with incentives column of Table1. MMULT multiplies values in each row of matrix on related incentives value and return the sum for the row (first column matrix value * first row incentives value + second column matrix value * second row incentives value).

On third step we generate current name filter on Names in Table2.

Finally multiply one on another and sum resulting vector to have total sum for the current name.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 15 2023 05:51 AM

Thankyou so much sir for this clarification

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 20 2023 11:33 PM

Hey@Sergei Baklan I need your little help, It was running good but there should be a condition that if a salesperson sell a model with lower of its price then no incentive will be given, we have to ignore that sales. How can we do that? I tried to use sumif and in last i gave condition that sale amount >= transpose of incentive model price amount but it give me error

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21 2023 01:49 AM

@ishaqib

I'm not sure where and how do you define prices. If like this

formula could be

```
=SUM(
MMULT( --(Table2[Model] = TRANSPOSE(Table1[Model])),
Table1[Incentive]) *
( MMULT( --(Table2[Model] = TRANSPOSE(Table1[Model])),
Table1[Price]) <= Table2[Actual price] ) *
(Table2[Name] = [@Name])
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21 2023 02:13 AM

Thankyou so muchhh sir, I got my answer dam rightt Thankyou again

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21 2023 04:47 AM

@ishaqib , you are welcome

1 best response

Accepted Solutions

best response confirmed by
ishaqib* (Copper Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07 2023 03:23 AM

SolutionWe may do step by step

In first step we return the matrix where each column corresponds to each model in Table1. Row indicates if such model is in related row of Table2 in Models. We use double dash "--" to convert TRUE and FALSE to 1 and 0 accordingly.

On second step we MMULT above matrix with incentives column of Table1. MMULT multiplies values in each row of matrix on related incentives value and return the sum for the row (first column matrix value * first row incentives value + second column matrix value * second row incentives value).

On third step we generate current name filter on Names in Table2.

Finally multiply one on another and sum resulting vector to have total sum for the current name.