Count / Sumproduct Help

[MJLG85]

Hello

I am trying to write a formula that will go through a column of numbers/receipt codes and will count how many times a set first two numbers appear (as the first two numbers determine a location)


any help would be appreceiated as I am struggling to write this forumla correctly

Thanks

 

[Aladin Akyurek]

Which column range?

And care to provide an example of what must be counted?

 

[MJLG85]

It will be Column I - Below is a lit of example numbers

250013475

90122975

170037827

90122435

90122799

120067617

<tbody>
</tbody><colgroup><col></colgroup>

 

[Aladin Akyurek]

And you have an example of what must be counted?

 

[MJLG85]

So it would be that column... how many times '90' appeared in the column

I have this one which is going through the column and everytime 90 appears in I its adding the value of it

=SUMPRODUCT((LEFT(Data!$I$2:$I$1000,2)=LEFT(B2,2))*Data!$J$2:$J$1000)

but I just need to know how many times 90 appears in column I


I just can't figure out how to just do a count of that column, I feel like its something obvious but i just cant see it

 

[MARZIOTULLIO]

=sumproduct(--isnumber(search(90,I2:I1000)))

 

[Aladin Akyurek]

So it would be that column... how many times '90' appeared in the column

I have this one which is going through the column and everytime 90 appears in I its adding the value of it

=SUMPRODUCT((LEFT(Data!$I$2:$I$1000,2)=LEFT(B2,2))*Data!$J$2:$J$1000)

but I just need to know how many times 90 appears in column I


I just can't figure out how to just do a count of that column, I feel like its something obvious but i just cant see it

=SUMPRODUCT((LEFT(Data!$I$2:$I$1000,2)=LEFT(B2,2))*Data!$J$2:$J$1000)

is testing the first two digits in I which equals to the first two digits of B2 and summing corresponding J values.

Counting the occurrences of the first two digit number of B2 as first two digits in I...

SUMPRODUCT(((LEFT(data!$I$2:$I$1000,2)=LEFT(B2,2))+0))

 

[MJLG85]

=SUMPRODUCT((LEFT(Data!$I$2:$I$1000,2)=LEFT(B2,2))*Data!$J$2:$J$1000)

is testing the first two digits in I which equals to the first two digits of B2 and summing corresponding J values.

Counting the occurrences of the first two digit number of B2 as first two digits in I...

SUMPRODUCT(((LEFT(data!$I$2:$I$1000,2)=LEFT(B2,2))+0))

Thank you... this works great!

 

[Aladin Akyurek]

Thank you... this works great!

You are welcome. Thanks for the update.