Find or If formula

[rockyw]

If I have Road number in A, The number of miles for the road in B, In D I want to enter the road I mowed, in E the number of miles I mowed on that road.
How can I find the road and miles and subtract from B and total in C. Would I use a match or find formula? The roads may be entered several times before the entire road is mowed.

 

[rockyw]

Could this be done with a macro?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would help us to understand better if you could provide some sample data, including some values entered in D and E, with the expected results calculated and entered manually in C and provide any further clarification in relation to that sample data. I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Do you only want a macro solution, or would a formula solution be okay if one is possible?

 

[rockyw]

In D write the road in. I E the miles mowed. In C would show miles left based on miles in B for each road. A formula would be fine. I tried many just cant get it to work. Thanks

 

[Peter_SSs]

• Thanks for updating your version details.
  
  
• Unfortunately, we cannot copy from an image to test, hence my request for XL2BB
  
  
• I don't understand the example. It looks like you have mowed 2 miles of road 65. If that is so, why is there only 2 miles left to go when road 65 is 5 miles long.
  
  
• Also, you originally mentioned that roads may be mentioned several times before completely mowed. Your example gives no indication of how that would be managed in columns C:E

 

[Rows N Columns]

Agree with @Peter_SSs , the header name of column C is bit misleading as the number reflects the miles mowed, and not the remaining miles not yet mowed.

If you want column C to show the miles mowed, you could enter below formula into cell C2 then copy the formula down:

=SUMIF($E:$E,$A2,$F:$F)

If you want column C to show the remaining miles not yet mowed, you can enter below formula into cell C2 then copy the formula down :

=B2-SUMIF($E:$E,$A2,$F:$F)

Would this work?

 

[rockyw]

OK thanks for the replies, I wasn't very clear. Id like to write in the road in D and miles in E. In C Id like to show the miles left for that road. So it would subtract the miles in B for each road and show what's left in C. I tried to copy selected range with the add in. Hope this is correct.

Twp Roads.xlsx
	A	B	C	D	E
1	Rd	Miles	Left	Mowed	Miles
2	10	1	1	65	5
3	27	4	4
4	28	3	3
5	29	2	2
6	30	1	1
7	31	2	2
8	32	3	3
9	60	5	5
10	61	3	3
11	62	1	1
12	65	5	0
13
14	Total Miles	30
15
16
17
18
19
20
21
22
23
24
25				Done	5
26				Left	25
Sheet1

Cell Formulas
Range	Formula
B14	B14	=SUM(B2:B13)
E25	E25	=SUM(E2:E24)
E26	E26	=30-E25

 

[Rows N Columns]

In that case, you should be able to use this formula

=B2-SUMIF($D:$D,$A2,$E:$E)

Enter into cell C2 and copy down for rest of cells in column C. See if this works for you?

 

[rockyw]

Thanks that works.