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.

[TABLE="width: 542"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Days[/TD]
[TD]Amt 1[/TD]
[TD]Amt 2[/TD]
[TD]Amt 3[/TD]
[TD]Total[/TD]
[TD]Example[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]2000[/TD]
[TD]500 + 500 + 500 + 500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]500 + 250 + 250[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]750[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]750[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]600[/TD]
[TD]1100[/TD]
[TD]600 + 500[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]500[/TD]
[TD]1100[/TD]
[TD]600 + 500[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]500[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]450[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2900[/TD]
[TD]1100[/TD]
[TD]450[/TD]
[TD]2900[/TD]
[TD]750 + 750 + 500 + 450 +450[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]750 + 750[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]750[/TD]
[TD]1500[/TD]
[TD]750 + 750[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]250[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1500[/TD]
[TD]2500[/TD]
[TD]250[/TD]
[TD]2500[/TD]
[TD]1000 + 1000 + 250 +250[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]750[/TD]
[TD]2750[/TD]
[TD]750 + 2000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]250[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]2000[/TD]
[TD]2750[/TD]
[TD]750 + 2000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]2250[/TD]
[TD]2750[/TD]
[TD]1000[/TD]
[TD]2250[/TD]
[TD]1000 + 250 +1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]450[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]550[/TD]
[TD]1550[/TD]
[TD]550 + 1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]2000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]3000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]1000[/TD]
[TD]1550[/TD]
[TD]550 + 1000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]1000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]8450[/TD]
[TD]1550[/TD]
[TD]2000[/TD]
[TD]8450[/TD]
[TD]450 + 2000 + 3000 + 1000 + 2000[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 272"]
<tbody>[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]250[/TD]
[/TR]
</tbody>[/TABLE]

 

[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/