[Solved] Looking for 3 different formulas

[sharyn]

Hi,
Q1: example table below, what I need to do is count the number of 'yes' total, except the first section, which is yes if any (so max 1).
Red yes
Blue yes
Green no

Banana no
Apple no
Orange yes

triangle yes
square no
circle no

so for this example, total would be 3.
I currently have this as 2 step, wondering if it can be done in one.

Q2: example table:
Mon 2nd Tues 3rd Wed 3th Thur 5th etc
pre pre post post

banana yes no no no
apple no yes no no

What I need to do is identify whether there is a yes for banana whilst 'pre'- the pre range is not fixed though, could be mon only, mon- tues, or mon- wed

Q3. I have 3 separate conditions, which need a value attributed:

<50 0
50- 80 5
>80 10

so if a cell has a value of 40, return a value of 0.
I can't work out how to do this for the middle range.

Thank you for your assistance.

 

[Yousuf Shovon]

Q1: example table below, what I need to do is count the number of 'yes' total, except the first section, which is yes if any (so max 1).

Hi Sharyn,

Welcome to ExcelDemy Forum! Thank you for sharing your concerns.

After going through your post, I am afraid I am not able to understand some points thoroughly. For instance, the table data you mentioned in Q1, are they in the same cell or different cells? However, I created 2 formulas for both cases.

So, for Q1, if the data are in the same cell, enter the below formula:

=SUM(IF(COUNTIF(A1:A3,"*yes*")>0, MIN(COUNTIF(A1:A3,"*yes*"), 1), 0), COUNTIF(A5:A7,"*yes*"), COUNTIF(A9:A11,"*yes*"))​

Consequently, you obtain the desired output.

​

If they are in separate cells, insert the following formula instead:

=COUNTIF(I5:J11,"yes")+IF(COUNTIF(I1:J3,"yes")>=1,1,0)​

Subsequently, we get the same output.

​

I am attaching the Excel file. Let me know your feedback. Thank you.

Regards,
Yousuf Shovon

 

[Yousuf Shovon]

Q3. I have 3 separate conditions, which need a value attributed:

For Q3,

Use the given formula:

=IF(A2 < 50, 0, IF(A2 <= 80, 5, 10))
​

As a result, we find the each corresponding values as you assigned.

​

Let me know if it works. I am attaching the desired workbook.

Regards,
Yousuf Shovon

 

[Yousuf Shovon]

Q2: example table:

I am afraid this question seems unclear. Can you share your workbook and explain it a bit more thoroughly?

Thank you.

 

[sharyn]

Hi Sharyn,

Welcome to ExcelDemy Forum! Thank you for sharing your concerns.

After going through your post, I am afraid I am not able to understand some points thoroughly. For instance, the table data you mentioned in Q1, are they in the same cell or different cells? However, I created 2 formulas for both cases.

So, for Q1, if the data are in the same cell, enter the below formula:

=SUM(IF(COUNTIF(A1:A3,"*yes*")>0, MIN(COUNTIF(A1:A3,"*yes*"), 1), 0), COUNTIF(A5:A7,"*yes*"), COUNTIF(A9:A11,"*yes*"))​

Consequently, you obtain the desired output.

View attachment 732
​

If they are in separate cells, insert the following formula instead:

=COUNTIF(I5:J11,"yes")+IF(COUNTIF(I1:J3,"yes")>=1,1,0)​

Subsequently, we get the same output.

View attachment 733
​

I am attaching the Excel file. Let me know your feedback. Thank you.

Regards,
Yousuf Shovon

That's perfect, thank you!

 

[sharyn]

For Q3,

Use the given formula:

=IF(A2 < 50, 0, IF(A2 <= 80, 5, 10))
​

As a result, we find the each corresponding values as you assigned.

View attachment 735
​

Let me know if it works. I am attaching the desired workbook.

Regards,
Yousuf Shov

I am afraid this question seems unclear. Can you share your workbook and explain it a bit more thoroughly?

Thank you.

Thank you, the other answers have worked perfectly.

I can't attach a workbook, but hopefully this snip will work:



What I need to calculate, is whether there is a yes for each scale, for the 'pre' days only. This window of 'pre' days is variable.

Is this clearer?

Thank you for your help! Greatly appreciated!

 

[Yousuf Shovon]

What I need to calculate, is whether there is a yes for each scale, for the 'pre' days only. This window of 'pre' days is variable.

Hello Sharyn,

Thank you for the thorough explanation and feedback. Based on your explanation, I have found a way to calculate whether there is a yes for each scale, for the 'pre' days only. And fortunately, the below formula works for the variable Pre rows.

Enter the below formula in I3:

=COUNTIFS($A$2:$Z$2, "Pre", INDEX($A$3:$Z$5, MATCH("Scale 1", $A$3:$A$5, 0), 0), "Yes")​

Thus, we obtain the total Yes count corresponding to Pre for Scale 1.



Note: For Scale 2 and Scale 3 count, manually type them in the above formula instead of Scale 1. Also, I have highlighted the rows that match the criteria manually for a better understanding.

I am attaching the desired workbook. Let us know your feedback.

Regards,
Yousuf Shovon

 

[Yousuf Shovon]

That's perfect, thank you!

You are welcome!

 

[sharyn]

Hello Sharyn,

Thank you for the thorough explanation and feedback. Based on your explanation, I have found a way to calculate whether there is a yes for each scale, for the 'pre' days only. And fortunately, the below formula works for the variable Pre rows.

Enter the below formula in I3:

=COUNTIFS($A$2:$Z$2, "Pre", INDEX($A$3:$Z$5, MATCH("Scale 1", $A$3:$A$5, 0), 0), "Yes")​

Thus, we obtain the total Yes count corresponding to Pre for Scale 1.

View attachment 747

Note: For Scale 2 and Scale 3 count, manually type them in the above formula instead of Scale 1. Also, I have highlighted the rows that match the criteria manually for a better understanding.

I am attaching the desired workbook. Let us know your feedback.

Regards,
Yousuf Shovon

Thank you so much.
There seems to be a bit of a change to what I snipped, there is one point in time that the change is from pre to post.
The pre- post info is also in a different sheet to the yes/no- will I need to change this to get this to work?

 

[Yousuf Shovon]

The pre- post info is also in a different sheet to the yes/no- will I need to change this to get this to work?

Hello Sharyn,

Definitely! With a slight modification in the formula, you can adjust to the new changes. I think you are referring to the changing Pre data to Post data now. And, the Post data are in different sheet for example the Parameter sheet.

Therefore, enter the below formula in I2,

=COUNTIFS(Parameter!$A$2:$Z$2, "Post", INDEX($A$2:$Z$4, MATCH("Scale 1", $A$2:$A$4, 0), 0), "Yes")​

As a result, you get the Total Count of Yes of Q2 sheet corresponding to Post columns in Parameter sheet.

I am attaching the solved workbook here. Feel free to ask any further questions.

Regards,
Yousuf Shovon