Find the sum of a specific value within multiple values

[Muhammed_Neamy]

Hi,

In our firm, we use one spreadsheet listing all invoices submitted under each separate project, sometimes the client transfers a lumpsum and we need to manually compare the invoices to find the correct breakdown for which invoices have been reimbursed. 

E.g. So let's suppose that a specific column has values from 1 to 9, and the client transferred an amount of 17, is there a formula that can deduce the possibilities that would tell me which invoices would match to 17, instead of me having to search manually and narrow it down to 8+9 ? (of course I'm talking about many invoices with amounts in thousands).

Thanks.

[#2]

Yes, there is a formula you can use to find the possibilities that add up to a specific value. The formula is called "SUMIF" and it allows you to sum up values in a range based on a specific criteria. Here's how you can use it in your case:

1. In a new column, enter the formula "=SUMIF(range,value)" where "range" is the range of values you want to check and "value" is the specific value you want to find the sum for. For example, if the column of values is in column A and the client transferred an amount of 17, the formula would be "=SUMIF(A:A,17)".

2. The result of the formula will be the sum of all the values in the range that match the specific value you entered. In your example, if there are values of 8 and 9 in the column, the formula would return a result of 17.

3. You can then use a filter or conditional formatting to highlight the rows that contribute to the sum. In Excel, you can use the "Filter" function to filter the values in the column based on the criteria you entered in the formula.

This should help you identify which invoices add up to the lump sum transferred by the client.

[#3]

@ajee10x 

Thank you for your reply, but it didn't work. it gives me the value of 0. from what I understood it it didn't find the value of 17 therefore it equaled 0.

what I want to know is which of the values in column A could equal 17, and how many if more than one option.

 

[#4]

@Muhammed_Neamy 

Another solution! You can use the Solver add-in in Excel to find the combination of values in the column that add up to 17.

Here are the steps to follow:

1. Select the cells containing the values in the column that you want to search.
2. Click on the "Data" tab in the Excel ribbon.
3. Click on the "Solver" button in the "Analysis" group.
4. In the Solver Parameters dialog box, set the following options:

• Set the "Set Objective" field to the cell where you want to display the sum of the selected values.
• Set the "To" field to "Value of" and enter 17.
• Set the "By Changing Variable Cells" field to the range of cells containing the values in the column that you want to search.

      5. Click on the "Options" button and make sure that the "Assume Linear Model" option is     unchecked.

     6. Click on the "Solve" button and wait for Solver to find a combination of values that add up to 17.

     7. If Solver finds a solution, click on the "OK" button to apply the solution to your worksheet.

Note that Solver may not always find a solution, especially if there are no combinations of values that add up to 17. In that case, you may need to adjust the values in the column or try a different approach. Also, Solver is an add-in that may need to be installed and enabled in Excel before you can use it.