Formula that finds specified numbers in rows only

[runelady]

Hi everyone!

Question.png

I have attached a visual example of my question. I am trying to find the number of occurrences for two numbers appearing in ROWS only.

Can anyone assist?

[Tony Valko]

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[Phuocam]

Try this ...

=SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:55")),0),"2")
*COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:55")),0),"3")>0))

[runelady]

Sorry, Tony. I have attached the file. The number combinations are along the top row.
runelady2.xlsx

[runelady]

Hi Phuocam,

This is very close. It's not picking up on zeros though, such as a 0/0 combo.

[runelady]

It doesn't seem to calculate them correctly in my sheet.

[Phuocam]

Sorry, edit ...

In A2:

=IF(LEFT(A$1)=RIGHT(A$1),SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),RIGHT(A$1))>1)),
SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),LEFT(A$1))
*COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),RIGHT(A$1))>0)))

Copy cross.

[Phuocam]

A little shorter ...

=SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),LEFT(A$1))
*COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),RIGHT(A$1))>0+(LEFT(A$1)=RIGHT(A$1))))

[samba_ravi]

a2

Please Login or Register  to view this content.

try this and copy towards right

[runelady]

Sorry, edit ...

In A2:

=IF(LEFT(A$1)=RIGHT(A$1),SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),RIGHT(A$1))>1)),
SUMPRODUCT(--(COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),LEFT(A$1))
*COUNTIF(OFFSET($A$3:$C$3,ROW(INDIRECT("1:313")),0),RIGHT(A$1))>0)))

Copy cross.

Thank you, Phuocam. This is perfect.

[Phuocam]

You are welcome!