Counif function

[ryan8200]

How to calculate the total fruits consumed by 3 students within 5 days ? Please take note each student can consume more than 1 per day ?

	Student 1	Student 2	Student 3
Day 1	apple / kiwi	orange	apple
Day 2	orange	honey dew	apple / kiwi
Day 3	kiwi	apple	orange
Day 4	honey dew / kiwi	kiwi	kiwi
Day 5	apple	kiwi	apple

<tbody>
</tbody>

Final Output

Apple : 6
Kiwi : 7
Orange : 3
Honey Dew: 2

 

[markmzz]

This is the output I get

Apple	7
Kiwi	9
Orange
Honey Dew

<tbody>
</tbody>

Why the value for orange & honey dew is 0 ? Does this mean I have to ensure all fruits is repeated before I use your formula?

Try this formula:

In B9

=SUMPRODUCT((LEN(" "&TRIM(SUBSTITUTE($A$2:$C$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($A$2:$C$6,"/"," / ")))&" /"," "&TRIM(UPPER($A9))&" /","")))/
LEN(" "&TRIM($A9)&" /"))

PS: you have extra spaces in some cells of the range A9:A12 in your workbook.

Markmzz

 

[ryan8200]

Try this formula:

In B9

=SUMPRODUCT((LEN(" "&TRIM(SUBSTITUTE($A$2:$C$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($A$2:$C$6,"/"," / ")))&" /"," "&TRIM(UPPER($A9))&" /","")))/
LEN(" "&TRIM($A9)&" /"))

PS: you have extra spaces in some cells of the range A9:A12 in your workbook.

Markmzz

It works !!! Mark

You are my hero

Have to use TRIM to remove the leading & trailing spaces

 

[ryan8200]

Try this formula:

In B9

=SUMPRODUCT((LEN(" "&TRIM(SUBSTITUTE($A$2:$C$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($A$2:$C$6,"/"," / ")))&" /"," "&TRIM(UPPER($A9))&" /","")))/
LEN(" "&TRIM($A9)&" /"))

PS: you have extra spaces in some cells of the range A9:A12 in your workbook.

Markmzz

Hi Mark,

What has caused the extra spaces ? How to avoid the same problem in future ?

 

[markmzz]

Hi Mark,

What has caused the extra spaces ? How to avoid the same problem in future ?

Hi Ryan,

In your workbook, in cells A11, you have Orange with a space in the final of the word and, in the cell A12, you have Honey Dew with a space in the final of the last word.

With the last formula below, you don't need to worry about the problem (the TRIM function remove the extra spaces for you).

In B9

=SUMPRODUCT((LEN(" "&TRIM(SUBSTITUTE($A$2:$C$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($A$2:$C$6,"/"," / ")))&" /"," "&[COLOR="#FF0000"]TRIM[/COLOR](UPPER($A9))&" /","")))/
LEN(" "&[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]TRIM[/COLOR]($A9)&" /"))

And copy down.

Markmzz

 

[ryan8200]

Hi Ryan,

In your workbook, in cells A11, you have Orange with a space in the final of the word and, in the cell A12, you have Honey Dew with a space in the final of the last word.

With the last formula below, you don't need to worry about the problem (the TRIM function remove the extra spaces for you).

In B9

=SUMPRODUCT((LEN(" "&TRIM(SUBSTITUTE($A$2:$C$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($A$2:$C$6,"/"," / ")))&" /"," "&[COLOR=#ff0000]TRIM[/COLOR](UPPER($A9))&" /","")))/
LEN(" "&[COLOR=#ff0000]TRIM[/COLOR]($A9)&" /"))

And copy down.

Markmzz

Markmzz[/QUOTE]


Hi Mark,

I'm still study on your formula.

Actually, it takes how many days for you to figure out the solutions ? Also, how do you come out with the solutions ? It's very compliacted.

If remove " " & " /" after the TRIM function, myapple will be counted as apple . This is what I have discovered beneath your formula.

 

[markmzz]

Markmzz

Hi Mark,

I'm still study on your formula.

Actually, it takes how many days for you to figure out the solutions ? Also, how do you come out with the solutions ? It's very compliacted.

If remove " " & " /" after the TRIM function, myapple will be counted as apple . This is what I have discovered beneath your formula.

Hi Ryan,

Use the evaluate formula feature of Excel to better understand how it works.

More information for evaluate formula feature :

Evaluate a nested formula one step at a time - Excel - Office.com

Markmzz