SUM yes & no in column's

[p_gillespie]

Hi all,

Im a newbie here and really struggling.

I have a master list of products
eg.
code product vegetarian contains gluten
1234 tomato Y N
5678 bacon N N
2468 bread Y Y

I'm using a vlookup to transfer this to another sheet and this is working so it pulls through items from the master list of products, however - and my problem is when i try to "sum" the column. I want a total column where if i try to "add" rows it will give me a yes or no over any number of items

ie.
code product vegetarian contains gluten
1234 tomato Y N
5678 bacon N N
TOTAL Y N

Please can someone help? will answer questions too.

Cheers

 

[Gerald Higgins]

Hi, welcome to the board.

What do mean by this exactly ?

I want a total column where if i try to "add" rows it will give me a yes or no over any number of items

Looking at your example, why are the TOTALS Y and N ? Why not N and N for example ?

 

[StevePr]

Hi all,

Im a newbie here and really struggling.

I have a master list of products
eg.
code product vegetarian contains gluten
1234 tomato Y N
5678 bacon N N
2468 bread Y Y

I'm using a vlookup to transfer this to another sheet and this is working so it pulls through items from the master list of products, however - and my problem is when i try to "sum" the column. I want a total column where if i try to "add" rows it will give me a yes or no over any number of items

ie.
code product vegetarian contains gluten
1234 tomato Y N
5678 bacon N N
TOTAL Y N

Please can someone help? will answer questions too.

Cheers

It's not entirely clear what you're asking but if you want the Total row to be Total Count(Y) Count(N)

then in C4 =COUNTIF(C1:C3,"Y") and in D4 =COUNTIF(D1:D3,"N")

Will give

code product vegetarian contains gluten
1234 tomato Y N
5678 bacon N N
2468 bread Y Y
Totals 2 2

 

[p_gillespie]

Apologies I've realised the flaw in my description of the problem.

If you think about it being a list of ingredients in a dish and you need to know if a dish is vegetarian or not. Ie if there is one or more Yes in the cells above before the next blank cell then the total should be Yes.

A dish could have 3 ingredients but it could have 12 ingredients.

Cheers
pete

 

[Gerald Higgins]

Presumably the logic in both cases needs to be slightly different, as follows:

For "vegetarian", if any single ingredient says N then the whole dish is N, otherwise Y.
If there is a mixture of Y and N, then N.

For "contains gluten", if any single ingredient says Y then the whole dish is Y, otherwise N.
If there is a mixture of Y and N, then Y.

See the difference ?

So, elaborating on StevePr's contribution, for vegetarian . . .
=(COUNTIF(C1:C3,"Y")>0,"N","Y")

and for contains gluten
=(COUNTIF(D1:D3,"Y")>0,"Y","N")

 

[p_gillespie]

So, elaborating on StevePr's contribution, for vegetarian . . .
=(COUNTIF(C1:C3,"Y")>0,"N","Y")

and for contains gluten
=(COUNTIF(D1:D3,"Y")>0,"Y","N")[/QUOTE]

Thats excellent thank you.

Is is there anyway of it changing how many cells to count if there were more ingredients eg. Count until reaching blank cell? Or if cell 'y'=number of ingredients 'x' count 'x' number of cells? So I don't have to manually change the D1:d3 every recipe

thanks

 

[StevePr]

So, elaborating on StevePr's contribution, for vegetarian . . .
=(COUNTIF(C1:C3,"Y")>0,"N","Y")

and for contains gluten
=(COUNTIF(D1:D3,"Y")>0,"Y","N")

Thats excellent thank you.

Is is there anyway of it changing how many cells to count if there were more ingredients eg. Count until reaching blank cell? Or if cell 'y'=number of ingredients 'x' count 'x' number of cells? So I don't have to manually change the D1:d3 every recipe

thanks[/QUOTE]
A realistic data sample would help but assuming that no matter the number of ingredients it's a maximum of three columns two easy options;

Place the total row at a position where it's below the maximum number of ingredients. Lots of blank space but easier maintenance. Say there are a max 28 rows Change the d1:d3 to d1:28. If you ever get a recipe with 30 rows cut and paste the total row to row 32 and excel will update the references.
Put your totals at the right of the Header row , eg Total Y in E1 then change the formula to be $C:$C for Vegetarian etc. Then it won't matter how many ingredients.​

Play around with putting the Totals in a Separate sheet would probably be useful but I'm not good enough with the Index and Match commands to offer a suggestion.

 

[p_gillespie]

Great ideas... Problem is my plan was to have many 'recipies' under each other on one sheet so can't count whole column. Otherwise yes I would do each on a seperate sheet


Sooo...
is it possible to reference a range

ie

range is r[-1]c:r[-'cell reference to give a value']c
where it would take the range as the cell above th total cell to the cell specified x number of rows up depending on number of ingredients. I'll post a dummy sheet tomorrow if this might work?

I've tried with indirect and can't get the syntax to work.

Cheers
pete