In Excel, I would like to count the ingredients which are in stock for certain recipes

user_10619851 0
Viewed 483

I have this table made in Excel in which the rows represent a (ceramics) recipe. Each column represents an ingredient. My first row is in use to mark (with an "X") an ingredient which I have in my stock. In column BM I count the number of ingredients needed per recipe.

Now I would like (per recipe) to count the ingredients I own, so I know which recipes are able to be mixed or are nearly able to be mixed by me. enter image description here

For example: The recipe "amberceladon" needs 7 ingredients (BM3). In the columns showing on the screenprint-image it's clear that I have "siliciumoxide" (AV1) in stock (the "X" in AV2). I would like to have expressed in BN3 the number "1". That way I know the "amberceladon" isn't to be mixed at this time, 'cause of lack of ingredients.

Just in case you wonder: the image doesn't show all columns, that's why the numbers in BM don't match what you see...

excel
4 Answers

If I understood correctly you want to count the total number of X per row.

Solution 1: you can just turn them into 1 and then use a SUM function

Solution 2: if you do not want to change the X, then make a table on the right with the following functions =IF(J36="X", 1, 0), it will transform an X into a 1, and you can count each row

user_134489930

It can be achieved by counting the cells with numbers. You can also use COUNTA

=COUNT(B2:M2)

enter image description here

user_115041280

I'm slightly confused by what you meant by your question but this is an excel sheet I made to track inventory for a local bakery. enter image description here

So for rows 4-15, cols B-E I've written how many ingredients I require for each recipe. The recipe name is written in 2B,2C,2E. The row beneath it displays how many orders there are of that item.
So for example if you want to find out how much sugar you require. You can do so by multiplying the number of cakes ordered and how much sugar you need for each cake and do that for each recipe and sum it. So for example for sugar you would have a formula like:
=$B$3*B5+$C$3*C5+$D$3*D5+$E$3*E5
and you would do this for all rows 4 to 14 and display them in column F. Column G stores the number of ingredients you already have. The H column shows how much of the ingredient you need to make/buy. So you subtract column G from F and store the result in H. So for example for Sugar you would do
=$F$5-$G$5
Hope this helps.

user_133560540

Starting with the little hint of using SUMPRODUCT I got ahead and figured it out at the end. This one is based on the complete table. In row 2 I changed the "X"'s into number 1, and the empty cells got a zero (0). So the numbers 1 represent an ingredient I already have in stock, the numbers 0 represent an ingredient I don't own.

=SUMPRODUCT(--(ISNUMBER(Table4[@[alberta-slib]:[zircopax]]));$E$2:$BL$2)

This did the trick for me!

enter image description here

user_106198510
Related