Count number times same value is present in a single Row of a larger range.

[ManinOZ]

G'Day,

I have been struggling with COUNTIFS, making a differnet countif/countifs for each row and adding them together but it just seems to count every time it sees each value, regardless if they are on the same line.


The range is C20:L147. The 15 values will be used all through the range over and over again. I want to count each time two specific values are present on the same row.

Thanks

[MarvinP]

Hi ManinOZ and welcome to the forum,

This sounds like a job for the Frequency function. See the attached where I've done a problem similar to yours above. See if it works and if not then modify the question and/or supply a sample workbook.

I used this formula and pulled down.

Please Login or Register  to view this content.

[MD PERVEZ KHAN]

If you would like to count the number of time a value is repeated in big data bank, then you can get it for each cell value in next column.
use this formula =countif(Range,criteria).
Example- if you want to check the number of time a cell value have been repeated just in next cell of different column

=countif($A$2:$A$1000,A2) (you will write this formula in B2- Then will come to know that the value in cell A2 is been repeated n number of time) just drag it to get the same result for each cells.

If you need let me know I will forward you the sample sheet.

If It solved your problem, then click on * BUTTON BELOW I.e- ADD REPUTATION

[ManinOZ]

Thanks, for your replies, I think however my question was not clear.

I have attached an example

[RedSummer]

Mr. Khan said it right,
But you only want to show a 1 (TRUE) if both 648 AND 642 are there, and a 0 (FALSE) if they BOTH are not there.

=IF( AND(COUNTIF(C22:K22, 648)>0,COUNTIF(C22:K22, 642)>0), 1, 0)

drag that down and then sum this new column

[Tony Valko]

I want to have the number of times both "648" and "642" appear in the same row

Will there ever be duplicates on a row?

648...648...642
642...640...642

[RedSummer]

Will there ever be duplicates on a row?

648...648...642
642...640...642

I don't think that is relevant to his question in the example sheet
"I want to have the number of times both "648" and "642" appear in the same row"

Relooking at what he wanted I realize my formula should be >1, not >0

Here you go:
=IF( AND(COUNTIF(C22:K22, 648)>0,COUNTIF(C22:K22, 642)>1), 1, 0)

[Tony Valko]

I don't think that is relevant to his question in the example sheet

At a quick glance I didn't see any dupes on any row.

However, it is relevant to my proposed solution.

Assuming there will not be dupes on any row:

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C20:K147,{642,648},0)),{1;1;1;1;1;1;1;1;1})=2))

That counts the entire range.

[RedSummer]

Oh apologies, Mr. Valko you are doing something way smarter and above my skills. Copying off of you,
If there are dupes, wouldn't doing something silly like

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C20:K147,{642,648},0)),{1;1;1;1;1;1;1;1;1})=2)) + SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C20:K147,{642,648,642},0)),{1;1;1;1;1;1;1;1;1})=3))

do work?

[Tony Valko]

No, that won't work.

Not sure how to account for dupes.

[RedSummer]

Odd, I tested it and it seemed to work...

[Tony Valko]

With just this data in the range:

C20 = 648
D20 = 648

Your formula returns 1. 642 is not present so the correct result should be 0.

[RedSummer]

Thank you for explaining that to me. I totally overlooked that.

So with that fix in,
I realize that we need

=IF( AND( IF(COUNTIF(C20:K20, 648)>0,TRUE,FALSE), IF(COUNTIF(C20:K20, 642)>0,TRUE,FALSE) ) , 1, 0)

In a new column and to sum that. I'm still trying to teach myself if you can do something with matrices for this.

[RedSummer]

OKAY

I think this does the trick!! One formula, cross references that each row has a 648 in it with each row has a 642 in it. For each row where both a 648 and 642 are in it, should say true or something, i don't really know. It made sense when I messed with it. XD

=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(C20:K147,{648},0)),{1;1;1;1;1;1;1;1;1})=1),--(MMULT(--ISNUMBER(MATCH(C20:K147,{642},0)),{1;1;1;1;1;1;1;1;1})=1))

[Tony Valko]

Like this...

=(COUNTIF(C20:K20, 648)>0)+(COUNTIF(C20:K20, 642)>0)

Then, do a COUNTIF(Range,2)

[ManinOZ]

There should no be any duplicates in the same row. sorry for the bad example data.

[Tony Valko]

There should no be any duplicates in the same row. sorry for the bad example data.

OK, then the formula in post #8 should do what you want.

[Tony Valko]

Just an FYI...

This will account for dupes.

Data Range

	A	B	C
5	1	1	1
6	1	2	3
7	2	2	3
8	2	2	1
9	------	------	------

Count the number of rows that contain both 1 and 2.

=SUMPRODUCT(--(MMULT(--(A5:C8=1),{1;1;1})>0),--(MMULT(--(A5:C8=2),{1;1;1})>0))