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

F

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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
 
Upvote 0
This is not a valid formula:
Code: 
[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?
 
Upvote 0
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]
 
Last edited:
Upvote 0
i want to create a formula like, =IF(SUM(D2:D8)=C2,C2,B2)
Click to expand...
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?

 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thank you Eric, But i want to know which are all in the list matches with B2 & which are all matches with C2.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
171
ED38
E
K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
96
kumara_faith
K
L
  • Solved
Formula for a Repeating Sequence
Replies
8
Views
809
Fluff
Fluff
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
633
madi1004
M
A
  • Solved
Replace formula with zero when populate Right mark for two sheets
Replies
3
Views
379
awoohaw
awoohaw

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top