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

jakobt

Active Member
Joined
May 31, 2010
Messages
334
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.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Apr 25, 2006
Messages
19,885
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top