You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I know I should be able to do this, but how do I add up all the values in one column which are lower than the corresponding values in another column?
For example (and this isn’t what it’s for, but hey, it’s a bike forum):
Suppose I was costing up my new bike build, and I’ve decided than I’m going to buy everything from one of 2 suppliers: Jones’ Cycles or China Reaction Cycles. So column A has the list of all the components I need, column B has the price at Jones’ and column C has the price at CRC.
So at the bottom of column B I want the sum of all the values which are cheaper at Jones’ and at the bottom of column C I want the sum of the items which are cheaper at China Reaction.
I know I could use MIN() to populate column D with the cheaper price for each item and then just SUM() that, but in this instance I need to know how much I’m spending in each store. I realise I could do it by writing a macro, but I’d rather not if I can avoid it. Surely there’s an easy way?
Thanks all!
SUMIF formula to add the lower values up?
Edited to add: Yeah, use SUMIF(b<c) etc.
If I was doing this I'd put an if statement in cols D & E to show a 1 for the cheaper price, then sumif the 1s in each column.
=if(B3 less than C3,"1","")
Sure there's gotta be a neater looking way, but that's how I'd do it
If you do sumifs you need a statement for each row, which could be messy.
I'd also create a new column of "IF Jones<CRC,Jones,CRC", then Sumif on that.
in single formulas:
Jones:
=SUM(IF(B1:B100<C1:C100,B1:B100)) entered as an array function Ctrl + Shift + Enter
CRC:
=SUM(IF(C1:C100<B1:B100,C1:C100)) entered as an array function Ctrl + Shift + Enter
in single formulas:
Jones:
=SUM(IF(B1:B100<C1:C100,B1:B100)) entered as an array function Ctrl + Shift + EnterCRC:
=SUM(IF(C1:C100<B1:B100,C1:C100)) entered as an array function Ctrl + Shift + Enter
Perfect! Thanks all, that's exactly what I was after.