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

[jakobt]

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)

 

[admiral100]

Hi,

Try this :

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

 

[Jonmo1]

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]

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)