Sum Values Based On A Cell Value From A Different Column

[eddmike]

Hi,

I am facing an issue making a grocery list that changes its cost according to what I purchase In an exact day.

Here's a mini sheet I created to see what I mean

Grocery List

Sheet1 Daily Cost >> Item,Price,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday Apple,1,X,X Orange,2.5,X,X,X Eggs,6,X,X Water,5,X,X,X,X,X Diet Coke,5,X,X Batteries ,10,X,X,X Breakfast,15,X,X,X,X Launch,25,X Coffee,7.5,X

docs.google.com

Example: Column C reflects my Monday purchases

I want to put the letter X on things I wanted to buy, Apple C3, Water C6 and Batteries C8, and I want that total cost of these to be reflected in C1, from Column B which has the Price of each of them

I tried to use Index Match, Vlookup and some Countif/s .. Nothing worked. I know it easy but Its kinda keep slipping from me

 

[etaf]

sumif()

In C1 and copy to d1, e1 etc
=SUMIF(C3:C11,"X",$B$3:$B$11)

result in C1, D1 etc

Grocery List.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Daily Cost >>		16	22.5	27.5	29.5	20	13.5	50
2	Item	Price	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3	Apple	1	X			X
4	Orange	2.5		X		X		X
5	Eggs	6				X		X
6	Water	5	X		X	X	X	X
7	Diet Coke	5		X			X
8	Batteries	10	X				X		X
9	Breakfast	15		X	X	X			X
10	Launch	25							X
11	Coffee	7.5			X
12
Sheet1

Cell Formulas
Range	Formula
C1:I1	C1	=SUMIF(C3:C11,"X",$B$3:$B$11)

 

[etaf]

you may not want this - But you could just put a quantity in

so if you had 6 eggs as an X - then a 1 - would be 6 - but if you put 2 , then that would be 12
same as coke, if you purchased packs etc

anyway - if you wanted to
sumproduct()

would look like this

Grocery List.xlsx
	A	B	C	D	E	F	G	H	I
1	Daily Cost >>		21	220	40	29.5	40	99.5	50
2	Item	Price	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3	Apple	1	1			1
4	Orange	2.5		4		1		3
5	Eggs	6				1		12
6	Water	5	2		2	1	2	4
7	Diet Coke	5		6			2
8	Batteries	10	1				2		1
9	Breakfast	15		12	1	1			1
10	Launch	25							1
11	Coffee	7.5			2
Sheet1

Cell Formulas
Range	Formula
C1:I1	C1	=SUMPRODUCT($B$3:$B$11,C3:C11)