0

I have a list that I'm checking against the main data.

The main data looks like:

1234  1
1235  1
1234  1
1213  2
1231  2
1212  2
1231  3
1231  3
etc

The list I'm checking against the main data is:

1
2
3
etc

For each number in my list, I want to count how many start with 123, so the output looks like:

ID  123
1  3
2  1
3  2
etc

I have each ID in the list already. To drag down for each number, I currently have countifs(a1:a8, a1,b1:b8, "123") and it's obviously producing an error. I know I need to include left somewhere in here but I'm not sure where or how to. Much thanks.

user47467
  • 1,045
  • 2
  • 17
  • 34

3 Answers3

2

In the Main Data sheet, add a column and enter formula as eg: C1=IF(LEFT(A1,3)="123",1,0). Drag the formula for every C cell. Then use that C cell in your SUMIFS in your list sheet eg: =SUMIFS(C:C,B:B,"="&E1) E col for me is your list.

Please refer screenshots below.

Identify begins with 123

sumifs to get the output

Edit:

Another Solution: =SUMPRODUCT(--(LEFT(Maindata!$A$1:$A$8,3)="123")*(Maindata!$B$1:$B$8=Maindata!D1)). This solution works fine to me.

sumproduct with --left

Abdul Hameed
  • 1,135
  • 1
  • 13
  • 24
0

You can use someproduct to do this:

=sumproduct((Maindata!$A$1:$A$8=A1)*(left(Maindata!$B$1:$B$8)="123"))

Where A1 holds the digit you're determining the amount of "values that start with 123" for, and the main data is in worksheet Maindata, range A1:B8.

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24
  • for some reason, this only inputs 1 for ever id that has 123, even if there's more than 1 in the range? – user47467 Jan 26 '16 at 17:04
  • @user47467 This formula entered in B1:B3, with A1:A3 holding the list as shown in OPs example and sample data in sheet Maindata A1:B8 gives exactly the output OP asks for. Please elaborate? This one can indeed be dragged down, since the reference to the main data is absolute, and the ID reference is not. – Rik Sportel Jan 26 '16 at 17:11
0

Is your data in the form of text or number values? If the former, your criteria should instead be "123*" (using an asterisk for wildcard), if the latter you might be able to get away with using ">1230".

asongtoruin
  • 9,794
  • 3
  • 36
  • 47