# =+sumif('absl new'!h:h,"7*",'absl new'!a:a)

#### jakobt

##### Active Member
Hi,

I have a table with accounts stating with numbers from 1-9.

I want to take a sumif of column h, off all accounts starting with 7. I have setup the following SUMIFS formula but it is not working:

=+SUMIF('ABSL NEW'!H:H,"7*",'ABSL NEW'!A:A)

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

##### Well-known Member
Hi,

Try this :

=SUMIF('ABSL NEW'!H:H,"=7*",'ABSL NEW'!A:A)

#### Jonmo1

##### MrExcel MVP
Are the values in column H actually numbers, or text strings?
The wildcard will only work if the values are TEXT strings..

"*7" will not match an account that is 7123456
But it would match an account that is say 7123456A <- The A at the end makes it a TEXT string.

Is the account number a fixed length? Say 10 digits?
If so, you can do this
=SUMIFS('ABSL NEW'!A:A,'ABSL NEW'!H:H,">=7000000000",'ABSL NEW'!H:H,"<8000000000")

#### pgc01

##### MrExcel MVP
Hi

You can use

=sumproduct(--(left('absl new'!h:h)="7"),'absl new'!a:a)

Remark: this type of formula will be more efficient if you use a range of rows instead of whole columns

If, for ex., you know that the data has less than 1000 rows you can use:

=sumproduct(--(left('absl new'!h1:h1000)="7"),'absl new'!a1:a1000)

Replies
6
Views
207
Replies
9
Views
327
Replies
22
Views
249
Replies
1
Views
174
Replies
8
Views
313

1,148,421
Messages
5,746,581
Members
424,032
Latest member
pochie2741

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back