Counif function

ryan8200 · Oct 10, 2013

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

Final Output

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

portucale · Oct 10, 2013

Thanks markmzz works perfectly, I think the originator will be happy, I can see some use for this formula in my case.

ryan8200 · Oct 10, 2013

markmzz said:
Maybe this:

Layout

Student 1
Student 2
Student 3

Day 1
apple / kiwi
orange
apple

Day 2
orange / myapple
honey dew
apple / kiwi

Day 3
kiwi / kiwi
apple
orange

Day 4
honey dew/kiwi
kiwi
kiwi

Day 5
apple
kiwi
apple

*

Final Output
Without repetition
With repetition

Apple
6
6

Kiwi
7
8

Orange
3
3

Honey Dew
2
2

************
*********************
***************
***********

<tbody>
</tbody>

Formulas

Code:

In B9 enter this formula =SUMPRODUCT(--ISNUMBER(SEARCH(" "&A9&" /"," "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /"))) In C9 enter this formula =SUMPRODUCT( (LEN(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($B$2:$D$6,"/"," / ")))&" /"," "&UPPER($A9)&" /","")))/ LEN(" "&$A9&" /"))

Markmzz

?

Thanks for your effort . Is that possible to simplify the formula that works for repetition ?

markmzz · Oct 11, 2013

portucale said:
Thanks markmzz works perfectly, I think the originator will be happy, I can see some use for this formula in my case.

You're welcome and thanks for the feedback.

Markmzz

markmzz · Oct 11, 2013

ryan8200 said:
?

Thanks for your effort . Is that possible to simplify the formula that works for repetition ?

Hi Ryan,

Sorry, but, until now, I don't know how to do that.

By the way, what is the problem with that formula?

Markmzz

ryan8200 · Oct 27, 2013

markmzz said:
Hi Ryan,

Sorry, but, until now, I don't know how to do that.

By the way, what is the problem with that formula?

Markmzz

Hi Mark,

Using your method for repetition, I can obtain value for kiwi but not for other fruits. Also, why in the name must be CAP in the final output ? Kindly clarify. Thanks

markmzz · Oct 28, 2013

ryan8200 said:
Hi Mark,

Using your method for repetition, I can obtain value for kiwi but not for other fruits. Also, why in the name must be CAP in the final output ? Kindly clarify. Thanks

Hi Ryan,

Here the formula Works (apple and kiwi). Look at this:

Layout

	Student 1	Student 2	Student 3
Day 1	apple / kiwi	orange	apple
Day 2	orange / myapple	honey dew	apple / kiwi
Day 3	kiwi / kiwi	apple	orange
Day 4	honey dew/kiwi	kiwi	kiwi
Day 5	apple / kiwi/Apple	kiwi	apple
*
Final Output	With repetition	Without UPPER
Apple	7	1
Kiwi	9	0
Orange	3	0
Honey Dew	2	0
************	*****************	***************	***********

Formulas

Code:

In B9

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

In C9

=SUMPRODUCT(
(LEN(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /"," "&$A9&" /","")))/
LEN(" "&$A9&" /"))

PS: see the difference with and without UPPER function. The function SUBSTITUTE need that function to work like you want.

Markmzz

ryan8200 · Oct 28, 2013

markmzz said:
Hi Ryan,

Here the formula Works (apple and kiwi). Look at this:

Layout

Student 1
Student 2
Student 3

Day 1
apple / kiwi
orange
apple

Day 2
orange / myapple
honey dew
apple / kiwi

Day 3
kiwi / kiwi
apple
orange

Day 4
honey dew/kiwi
kiwi
kiwi

Day 5
apple / kiwi/Apple
kiwi
apple

*

Final Output
With repetition
Without UPPER

Apple
7
1

Kiwi
9
0

Orange
3
0

Honey Dew
2
0

************
*****************
***************
***********

<tbody>
</tbody>

Formulas

Code:

In B9 =SUMPRODUCT( (LEN(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&UPPER(TRIM(SUBSTITUTE($B$2:$D$6,"/"," / ")))&" /"," "&UPPER($A9)&" /","")))/ LEN(" "&$A9&" /")) In C9 =SUMPRODUCT( (LEN(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /")-LEN(SUBSTITUTE(" "&TRIM(SUBSTITUTE($B$2:$D$6,"/"," / "))&" /"," "&$A9&" /","")))/ LEN(" "&$A9&" /")) PS: see the difference with and without UPPER function. The function SUBSTITUTE need that function to work like you want.

Markmzz

But it wouldn't work for me, all value is 0. Should I use ctrl+shft+del to enter the formula ?

What is purpose for using "/", " / " & " /" ? Are they the same ?

Here is the code I modified from yours

Code:

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

markmzz · Oct 28, 2013

ryan8200 said:
But it wouldn't work for me, all value is 0. Should I use ctrl+shft+del to enter the formula ?

What is purpose for using "/", " / " & " /" ? Are they the same ?

Here is the code I modified from yours

Code:

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

Hi Ryan,

You need to use my formula (with UPPER function - did you read my last post - look at the column Without UPPER in that post).

Now, look at this (with your layout):

Layout

	A	B	C
1	Student 1	Student 2	Student 3
2	apple / kiwi	orange	apple
3	orange / myapple	honey dew	apple / kiwi
4	kiwi / kiwi	apple	orange
5	honey dew/kiwi	kiwi	kiwi
6	apple / kiwi/Apple	kiwi	apple
7	*
8	Fruits	Final Output
9	Apple	7
10	Kiwi	9
11	Orange	3
12	Honey Dew	2
****	*****************	*****************	*****************

Formula

Code:

In B9 (use only Enter to enter the formula)

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

"What is purpose for using "/", " / " & " /" ? Are they the same ?"

I used this to find the exactly word that you want (like apple and not myapple).

Markmzz

ryan8200 · Oct 28, 2013

markmzz said:
Hi Ryan,

You need to use my formula (with UPPER function - did you read my last post - look at the column Without UPPER in that post).

Now, look at this (with your layout):

Layout

A
B
C

1
Student 1
Student 2
Student 3

2
apple / kiwi
orange
apple

3
orange / myapple
honey dew
apple / kiwi

4
kiwi / kiwi
apple
orange

5
honey dew/kiwi
kiwi
kiwi

6
apple / kiwi/Apple
kiwi
apple

7
*

8
Fruits
Final Output

9
Apple
7

10
Kiwi
9

11
Orange
3

12
Honey Dew
2

****
*****************
*****************
*****************

<tbody>
</tbody>

Markmzz

This is the output I get

Apple	7
Kiwi	9
Orange	0
Honey Dew	0

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?

markmzz · Oct 29, 2013

ryan8200 said:
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?

Look at your PM Box.

Markmzz

Counif function

ryan8200

Active Member

portucale

Board Regular

Excel Facts

ryan8200

Active Member

markmzz

MrExcel MVP

markmzz

MrExcel MVP

ryan8200

Active Member

markmzz

MrExcel MVP

ryan8200

Active Member

markmzz

MrExcel MVP

ryan8200

Active Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Counif function

Active Member

Board Regular

Excel Facts

Active Member

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock