Adding numbers that are only highlighted

[shaftdx]

Ok I hope I can do this and I hope I can get across what I mean.

I have an ever growing list of trips my company provides. I have 4 vendors that we quote the work out to. I sent out the trip and get the quoted price back for either of them. I choose what company is the cheapest and that price automatically Highlights.

I would like to find a way to have a "Summery" tab that will automatically not only add all the highlighted prices but also divide them up by the month so I can more easily do the invoicing.

I am sorry the page has client info so I can nod share a screen shot or anything like that.

does this make sense? I can go into more detail if needed

 

[etaf]

how do you choose the company - and how is it automatically highlighted ?
Theres no inbuilt function to sum by colour - But you could have an extra column that has something in and then use a sumif()
based on whatever you put in the helper column

otherwise you will need VBA , nolonger my area on forums , as out of date

Book8
	A	B	C	D
1	Quote	Price	Lowest	SUM()
2	Quote 1	100		210
3	Quote 2	200
4	Quote 3	70	x
5	Quote 4	100
6	Quote 5	450
7	Quote 1	100
8	Quote 2	200
9	Quote 3	70	x
10	Quote 4	100
11	Quote 5	450
12	Quote 1	100
13	Quote 2	200
14	Quote 3	70	x
15	Quote 4	100
16	Quote 5	450
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMIF(C2:C16,"X",B2:B16)

 

[shaftdx]

how do you choose the company - and how is it automatically highlighted ?
Theres no inbuilt function to sum by colour - But you could have an extra column that has something in and then use a sumif()
based on whatever you put in the helper column

otherwise you will need VBA , nolonger my area on forums , as out of date

My column L has a pull down menu with a list of all available vendors.

Columns R thru U are where I put the prices they quoted. So Col R is Vendor #1 Col S is Vendor #2 and so on. I used Conditional Formatting that when I choose Vendor #1 in col L Row 200 the corresponding cell in R200 highlights. so I can tell just by looking at it even though all 4 vendors bid on that trip it was vendor #1 that was awarded it.

 

[etaf]

not sure from that info , how to summarise, as i say if VBA , then not my area, which will only sum by colour

But it does sound like you could use the vendors selected in column L

really need to see a sample

I am sorry the page has client info so I can nod share a screen shot or anything like that.

Is it possible to perhaps change all the names - overwrite with A1 , and copy down
OR make a small sample

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

XL2BB is an add-in used on this forum , see my signature or the menu in post to link to the full details
OR put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Here is the link to XL2BB instructions

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[shaftdx]

not sure from that info , how to summarise, as i say if VBA , then not my area, which will only sum by colour

But it does sound like you could use the vendors selected in column L

really need to see a sample

Is it possible to perhaps change all the names - overwrite with A1 , and copy down
OR make a small sample

Sorry I am still rather new to this part of the message board. I can not get the XL2BB to work I think i need Admin approval I was able to load the sample spread sheet to One drive but I do not know how to get it on here. Thank you for your help and your time!

 

[etaf]

I can not get the XL2BB to work I think i need Admin approval

if its a work computer, then it may have restrictions

I was able to load the sample spread sheet to One drive but I do not know how to get it on here.

you should be able to see on onedrive a share option, and then you can copy the link and post here

here are instruction & a video on how to share files from onedrive

Share files and folders in OneDrive (personal) - Microsoft Support

Training: With Microsoft OneDrive, share your work with others, control who can view and edit, and work together at the same time. Watch this online training video to see how.

support.microsoft.com

 

[mikerickson]

I used Conditional Formatting that when I choose Vendor #1 in col L Row 200 the corresponding cell in R200 highlights. so I can tell just by looking at it even though all 4 vendors bid on that trip it was vendor #1 that was awarded it.

Rather than sum based on what color column R is, sum based on what value column L has.
=SUMIF(L1:L1000, "Vendor 1", R1:R1000)

 

[shaftdx]

Rather than sum based on what color column R is, sum based on what value column L has.
=SUMIF(L1:L1000, "Vendor 1", R1:R1000)

Luckily I have insomnia right now and this is exactly what I had in mind thank you!
is there a way I can further break it down by month?

 

[mikerickson]

SUMIFS lets you look at many columns

 

[shaftdx]

SUMIFS lets you look at many columns

=SUMIF(Trips!L:L,A6,Trips!U:U) is the formula I am using to add up all the numbers and it is working great, Thank you! Does it matter where in the formula I put the next criteria for the Months? if it helps this formula is in Cell B6. above that in B2 I have "August" B3 is 8/1/2021 & B4 is 8/31/2021

I think there is away to get it to only count the numbers between the 2 dates but I do not remember how.