Counif function

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Oct 10, 2013

• #2

Hi,

Assuming your source table is in the range A1:D6 (including the column and row labels, as you give), and that your results table is in F1:G4, enter this formula in G1 and copy down as required:

=SUMPRODUCT(--(ISNUMBER(SEARCH(F1,$B$2:$D$6))))

Regards

 

Upvote 0

R

ryan8200

Active Member

Joined

Aug 21, 2011

Messages

357

• Oct 10, 2013

• #3

XOR LX said:

Hi,

Assuming your source table is in the range A1:D6 (including the column and row labels, as you give), and that your results table is in F1:G4, enter this formula in G1 and copy down as required:

=SUMPRODUCT(--(ISNUMBER(SEARCH(F1,$B$2:$D$6))))

Regards

Click to expand...

Thanks for your effort. But what will happen if another fruit also has similar string such as: XXXA & XX. Will the formula still workable with accurate output ?

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Oct 10, 2013

• #4

Will you potentially have the same fruit consumed twice/more times by the same person on the same day so the data would look like:


Day 1______apple/kiwi/apple

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Oct 10, 2013

• #5

Sorry. Not sure I follow. What do you mean by "similar string such as: XXXA & XX"? Can you give a concrete example involving fruit names, as in your original post?

Regards

 

Upvote 0

R

ryan8200

Active Member

Joined

Aug 21, 2011

Messages

357

• Oct 10, 2013

• #6

XOR LX said:

Sorry. Not sure I follow. What do you mean by "similar string such as: XXXA & XX"? Can you give a concrete example involving fruit names, as in your original post?

Regards

Click to expand...

Let say I want to calculate separately for GRAPE & GRAPEFRUIT , will the formula still workable ?

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Oct 10, 2013

• #7

You could amend that formula to:

=SUMPRODUCT(--ISNUMBER(SEARCH("/"&F1&"/","/"&$B$2:$D$6&"/")))

But my query above is still relevant

 

Upvote 0

R

Randel

New Member

Joined

Oct 10, 2013

Messages

3

• Oct 10, 2013

• #8

=countif($j$3:$l$10,i12)

=countif(range,(cell for the name of the fruit)

try this..

The one sent by xor lx ​is also correct..

 

Upvote 0

P

portucale

Board Regular

Joined

Jun 22, 2006

Messages

85

• Oct 10, 2013

• #9

Hi Ryan,

ryan8200 said:

Let say I want to calculate separately for GRAPE & GRAPEFRUIT , will the formula still workable ?

Click to expand...

Yes that is the case because if you have GRAPE and GRAPEFRUIT then the results will be:

GRAPE - 2
GRAPEFRUIT - 1

So any idea how we go around this?

 

Upvote 0

M

markmzz

MrExcel MVP

Joined

May 7, 2011

Messages

3,699

• Oct 10, 2013

• #10

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

 

Upvote 0