sumproduct(countif combo

[leader2k]

Hi new comer need help with the following formulas

i have 6 columns a2/a100 to f2/f100 with (6 numbers)each row / and i have h2to m2 (6 numbers)

i'm using this formula'=SUMPRODUCT(COUNTIF($H$1:$M$1,A2:F2))' it's working but what i need is instead of showing all the count from 0 to 6 ,i need from 3 to 6 in VBA if possible.

it will be appreciated.

 

[Hui]

Leader2K


Firstly, Welcome to the Chandoo.org Forums


Can you explain what 0 to 6 means and 3 to 6 ?


Can you post a sample file of data

refer the sticky green post at the main Chandoo.org Forum page

 

[leader2k]

Well thanks a lot

and here is a sample

DATA OUTPUT NUMBERS TO CHECK


A2///B2//C2//D2//E2//F2 D2 H2///J2///I2///K2///L2///M2

1 2 3 4 5 13 0 no 7 8 9 11 12 6

1 2 3 4 5 6 1 no

7 8 9 1 2 3 3 yes

7 8 9 11 12 6 6 yes

So what i need is the output is a matching 3 numbers and greater,in VBA if possible .

very appreciated

My Regards.

 

[leader2k]

Sorry Hui discard the previous post it was a formating mishab

my project is a lottery like so when verifying the draw i need only the matching 3-4-5-6

numbers to show in the output column.

thanks .

 

[Hui]

Leader2k


I'm still confused


can you please post a sample file with either better instructions or a sample of what your input and output requirement is

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[leader2k]

Hi

sorry about the confusion and i appreciat your patience

here is a link to the sample,if you look at the output i don't need the 0-1-2 matches

i need it to display when it maches 3-4-5-6 (VBA if possible)

thanks hopefully this time i've been more to the point.

My regards

https://www.dropbox.com/s/hilrdlpszoys5tr/Sum_Count.xlsx

 

[NARAYANK991]

Hi ,


Try this :


=SUM(IF(IFERROR(MATCH($H$2:$M$2,$A3:$F3,0),0),1,0))


entered as an array formula , using CTRL SHIFT ENTER.


Enter this in G3 , and copy down.


Narayan

 

[leader2k]

Thanks NARAYANK991 for the reply ,but your formulas(array) gave me the same result as the one already using' =SUMPRODUCT(COUNTIF($H$2:$M$2,A3:F3))' i need the matches 3 and above showing not the 0-1-2 showing.

My regards.