CODEWITHSUNDEEP
excelcountif
user47467
user47467

Reputation: 1093

Excel combining countifs and left function

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.

Upvotes: 0

Views: 7258

Answers (3)

Abdul Hameed
Abdul Hameed

Reputation: 1145

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

Upvotes: 2

asongtoruin
asongtoruin

Reputation: 10359

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".

Upvotes: 0

Rik Sportel
Rik Sportel

Reputation: 2679

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.

Upvotes: 0

Related Questions