VBA Code for creating a formula for adding specific values (=IF(A2:A10)=B2,B2,C2))

[Feroz90]

Hi All,

In the below example, I want to add each days splitted amount in Amt 3 and reconcile with Amt 1 & Amt 2, say for example, For Monday, if we add first 4 rows in Amt 3, we get 2000 which is in Amt 2 and if we add next 3 rows of Monday , we get 1000 which is in Amt 1. Anyone's help with any VBA code will be appreciated.

Days	Amt 1	Amt 2	Amt 3	Total	Example
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	500	2000	500 + 500 + 500 + 500
Monday	1000	2000	250	1000	500 + 250 + 250
Monday	1000	2000	250	1000	500 + 250 + 250
Monday	1000	2000	500	1000	500 + 250 + 250
Tuesday	2900	1100	750	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	750	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	600	1100	600 + 500
Tuesday	2900	1100	500	1100	600 + 500
Tuesday	2900	1100	500	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	450	2900	750 + 750 + 500 + 450 +450
Tuesday	2900	1100	450	2900	750 + 750 + 500 + 450 +450
Wednesday	1500	2500	750	1500	750 + 750
Wednesday	1500	2500	750	1500	750 + 750
Wednesday	1500	2500	1000	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	1000	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	250	2500	1000 + 1000 + 250 +250
Wednesday	1500	2500	250	2500	1000 + 1000 + 250 +250
Thursday	2250	2750	1000	2250	1000 + 250 +1000
Thursday	2250	2750	750	2750	750 + 2000
Thursday	2250	2750	250	2250	1000 + 250 +1000
Thursday	2250	2750	2000	2750	750 + 2000
Thursday	2250	2750	1000	2250	1000 + 250 +1000
Friday	8450	1550	450	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	550	1550	550 + 1000
Friday	8450	1550	2000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	3000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	1000	1550	550 + 1000
Friday	8450	1550	1000	8450	450 + 2000 + 3000 + 1000 + 2000
Friday	8450	1550	2000	8450	450 + 2000 + 3000 + 1000 + 2000

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

Thanks you in advance

 

[Feroz90]

Hi All,

All I need is to get a formula of =IF(A2:A10)=B2,B2,C2) Lets say, A2:A10 have 100 each and in B2 I have 500, so if the use this formula i need to get 500 or else anything in C2. Appreciate for helping me.

I use this code to finding specific value in the list, can any one give me an idea or vba code for the below.

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice 20160506
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
For Each xCell In CoinsRange
If Not (xSum / xCell < 1) Then
xStr = xStr & "1 of " & xCell & " "
' xSum = xSum - xCell
End If
Next
GetCombination = xStr
End Function

 

[Joe4]

This is not a valid formula:

[COLOR=#333333]=IF(A2:A10)=B2[/COLOR]

Checking for a whole range of values equal to one cell really does not make any sense.
What is it exactly you are trying to do?
Are you trying to see if the SUM of A2:A10 is equal to B2?
Or are you trying to see if every cell in A2:A10 is equal to B2?

 

[Feroz90]

Hi,

I want it like, in the below table if you add 500 + 500 + 500 + 500, we get the C2, i.e 2000, the remaining amount will cover the B2, so i want to create a formula like, =IF(SUM(D2:D8)=C2,C2,B2), Where C2 = 2000, B2 = 1000. Thanks

Monday	1000	2000	500
Monday	1000	2000	500
Monday	1000	2000	500
Monday	1000	2000	500
Monday	1000	2000	500
Monday	1000	2000	250
Monday	1000	2000	250

<tbody>
</tbody>

 

[Joe4]

i want to create a formula like, =IF(SUM(D2:D8)=C2,C2,B2)

Didn't you just do that (create the formula)?

=IF(SUM(D2:D8)=C2,C2,B2)
is a valid formula.
What is the issue with it?

 

[Feroz90]

Hi,

Please look at the example, if you Sum the Column 3, you will get 3000 and not 2000, all I need is to get only 2000, so i want to create a formula in this method.

=IF(SUM(D2:D8)=C2,C2,B2), Here, (D2:D8)=(500:500:500:500:500:250:250), C2 = 2000, B2 = 1000.

Thanks

 

[Joe4]

So, you want to know if adding up the values from the last column (D), equal the value in the first line of column (C)?
Is that right?
Do the values always have to be in order in column D?
The only way I know of doing something like that would be to use VBA.
Maybe someone knows of a way with some sort of array functions, but I do not.

 

[Eric Golf]

Hi Feroz90,

A guess but are you actually looking for C2 to contain the sum of D2:D8 up to a maximum of 2000, and if the sum of D2:D8 is greater than 2000 then B2 should contain the remainder?

If so:-
In C2 put =MIN(2000,SUM(D2:D8))
In B2 put =SUM(D2:D8)-C2

Note - Formulas in Excel only populate the cell they are in.

Hope this helps,

Eric.

 

[Feroz90]

Thank you Eric, But i want to know which are all in the list matches with B2 & which are all matches with C2.

 

[Feroz90]

I also tried the below link, but it not working for me.

https://www.get-digital-help.com/2011/02/22/excel-udf-find-numbers-in-sum/