What Formula to use??

madcabbie

New Member
Joined
May 15, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi, I am still learning more advanced excel functions such and Index Match, Sumifs, etc. I have a task which I can't get right. I want to get the total sum when selecting each of the outlets below in Column AH. I need to get this information by using the Cheque number column but it has alpha and numeric in the cell. I know I should be able to use something like sumifs and Left function but I keep getting errors. If I delete the numbers and just have the 3 alpha characters it works but I need to keep as the below because the data comes from an import. If someone could provide the easiest formula for me to input that would be great. I am sure there would be several out there :)

Small side note, but I am hoping it shouldn't make much different other than tab naming labels, but Column Q, AH and Total Sum below will be on a separate tab, drawing figures from an input tab. I have combined here for easy viewing.

Thanks in advance.

CHEQUE_NUMBERCASHIER_CREDITOutlet Column AHTotal Sum
ILK008120210510214224
106.5​
ILKNeed Formula???
ILK008720210510195005
93​
ARN
ILK010820210510211708
170​
MAN
ILK011520210510220706
239.75​
BOU
ILK011720210510223908
154​
GEC
WAL4403520210510112251
72​
PIO
WAL4404520210510125927
57​
WAL4404620210510125236
64​
WAL4405920210510122447
42​
WAL4411920210510153442
69​
WAL4415720210510171928
111.5​
WAL4417620210510184124
51.25​
WAL4419620210510232040
76.99​
WAL4422120210510193742
53.18​
WAL4426720210510215810
12​
PIR4008020210510161506
0​
PIR4006320210510153124
0​
ILK000320210510070414
0​
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
See if this works for you.

20210515 Sumifs with WildCard.xlsx
ABCDEFG
1CHEQUE_NUMBERCASHIER_CREDITOutlet Column AHTotal Sum
2ILK008120210510214224106.5ILK761.25
3ILK00872021051019500593ARN0
4ILK010820210510211708170MAN0
5ILK011520210510220706237.75BOU0
6ILK011720210510223908154GEC0
7WAL440352021051011225172PIO0
8WAL440452021051012592757WAL608.92
9WAL440462021051012523664PIR0
10WAL440592021051012244742
11WAL441192021051015344269
12WAL4415720210510171928111.5
13WAL441762021051018412451.25
14WAL441962021051023204076.99
15WAL442212021051019374253.18
16WAL442672021051021581012
17PIR40080202105101615060
18PIR40063202105101531240
19ILK0003202105100704140
Sheet1
Cell Formulas
RangeFormula
G2:G9G2=SUMIFS($B$2:$B$19,$A$2:$A$19,E2&"*")
 
Upvote 0
See if this works for you.

20210515 Sumifs with WildCard.xlsx
ABCDEFG
1CHEQUE_NUMBERCASHIER_CREDITOutlet Column AHTotal Sum
2ILK008120210510214224106.5ILK761.25
3ILK00872021051019500593ARN0
4ILK010820210510211708170MAN0
5ILK011520210510220706237.75BOU0
6ILK011720210510223908154GEC0
7WAL440352021051011225172PIO0
8WAL440452021051012592757WAL608.92
9WAL440462021051012523664PIR0
10WAL440592021051012244742
11WAL441192021051015344269
12WAL4415720210510171928111.5
13WAL441762021051018412451.25
14WAL441962021051023204076.99
15WAL442212021051019374253.18
16WAL442672021051021581012
17PIR40080202105101615060
18PIR40063202105101531240
19ILK0003202105100704140
Sheet1
Cell Formulas
RangeFormula
G2:G9G2=SUMIFS($B$2:$B$19,$A$2:$A$19,E2&"*")
Worked perfectly, legend. Thanks.
 
Upvote 0
Glad it worked for you. Happy to have been able to help. I am constantly learning new things myself.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top