Help Needed With A Formula Please

[angelique7]

Hi all,

I hope someone can help me, please.
I am busy working on a spreadsheet and trying to streamline it a bit.
I am trying to reduce the number of columns on the sheet, as it's a rather big spreadsheet, and involves a lot of scrolling from left to right.
I would like to find out if someone can help me with a formula for the following: (I want to include a summary at the bottom of the spreadsheet)

Cell C24 must contain the TOTAL number of km's travelled for "staff" - the formula must check if column C contains "staff" and then deduct the value in column D from column E

I was using a column with a formula to sum whatever info I wanted, but I want to reduce the number of columns in this sheet. I have attached a small sample of the sheet.
I would appreciate any advice or assistance.
Many thanks,
Angelique

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[angelique7]

06.07.2022.xlsx
	I
20
Sheet1

Hi,

This is my first time using XL2BB - Please let me know if I've done so correctly?

Thank you

 

[Fluff]

You need to select the range first, which from the image looks to be C8:F25

 

[angelique7]

06.07.2022.xlsx
	B	C	D	E	F	G	H	I	J
8			Opening	Closing
9		MC3	58520.00	58527.00
10		Staff	58527.00	58535.00	8.00	I want to get rid of column F completely
11		Staff	58535.00	58546.00	11.00
12		Unloaded	58546.00	58558.00
13		Staff	58558.00	58570.00	12.00
14		Staff	58570.00	58582.00	12.00
15		MC4	58582.00	58593.00
16		Unloaded	58593.00	58605.00
17		Staff	58605.00	58639.00	34.00
18		Collecting Order from Supplier	58639.00	58651.00
19		Collecting Order from Supplier	58651.00	58676.00
20
21
22	Summary:
23		KM's
24	Staff	77.00	Cell C24 would contain the TOTAL number of km's travelled for "staff" - the calculation must search / lookup if column C contains "staff" and then deduct the value in column D from column E
25
26
27
28
Sheet1

Cell Formulas
Range	Formula
F10:F17	F10	=IF(C10="Staff",E10-D10,"")
D10:D19	D10	=E9
C24	C24	=SUM(F10:F19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C11	Cell Value	contains "No Transport Charged"	text	NO
C11	Cell Value	contains "Free Delivery"	text	NO
C19:C21,C23	Cell Value	contains "No Transport Charged"	text	NO
C19:C21,C23	Cell Value	contains "Free Delivery"	text	NO
C9:C10,C12:C18	Cell Value	contains "No Transport Charged"	text	NO
C13	Cell Value	contains "Free Delivery"	text	NO
C9:C10	Cell Value	contains "Free Delivery"	text	NO
C12,C14:C18	Cell Value	contains "Free Delivery"	text	NO
E9:E23	Cell Value	duplicates	text	NO

Okay - I hope i've done it right this time

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	B	C	D	E
8			Opening	Closing
9		MC3	58520	58527
10		Staff	58527	58535
11		Staff	58535	58546
12		Unloaded	58546	58558
13		Staff	58558	58570
14		Staff	58570	58582
15		MC4	58582	58593
16		Unloaded	58593	58605
17		Staff	58605	58639
18		Collecting Order from Supplier	58639	58651
19		Collecting Order from Supplier	58651	58676
20
21
22	Summary:
23		KM's
24	Staff	77
25
Original

Cell Formulas
Range	Formula
D10:D19	D10	=E9
C24	C24	=SUM(FILTER(E9:E19-D9:D19,C9:C19=B24))

 

[angelique7]

It works perfectly! Thank you SO much, I really appreciate your patient and very quick and efficient help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help Needed With Nested Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[angelique7]

Okay thank you for letting me know. I should have deleted my post on ExcelForum before I posted here, but I was getting frustrated with the site. Please accept my apologies. I am very sorry.
Going forward, when I need Excel help again, I'd rather post in MREXEL.COM, as I was very happy with the patient help I received today.

Thank you again, and kindest regards,

Angelique