# Expanded SUMIF question

#### tony0682

##### Board Regular
Thanks.. Now.. Here is a more specific question with more questions about SUMIF.

Assume these are all on different sheets.

In Sheet 3, column I I'm trying to SUM the numbers in column B if the 5 leftmost digits is equal to Column F. I can't use LEFT of a range. I also want to use a vlookup in the criteria if its possible. The letters in E and H are just theoretical names.. For example, I'm trying to do..
=SUMIF(LEFT(\$A\$3:\$A\$22,5),VLOOKUP(H3,\$E\$3:\$F\$12,2),\$B\$3:\$B\$22)
Book8
ABCDEFGHIJ
1Sheet 1Sheet 2Sheet 3
2
3004375BN0977,125.00A00437A0
403072SWP6679,067.00B03072B0
504541GGQ9973,916.00C04541C0
61266712T260,082.00D12667D0
71266715H5550,368.00E22541F0
8126671Y91508,321.00F32027G0
9126673BR2580,375.00G36228H0
10126673JQ6986,057.00H45254J0
1112667FCW3919,159.00I54444K0
1232027NHB0656,923.00J58552
1336228FD60715,449.00K58992
1445254NGH4179,705.00
1545254NGP6775,822.00
1645254NGW1516,267.00
1745254NKD8305,374.00
1845254NKF3299,423.00
1945254TPU2458,185.00
20585525FC7893,532.00
215899292M9521,060.00
225899295V6905,138.00
Sheet1

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Assuming F3:F13 contain numbers formatted as 00000:

=SUMIF(\$A\$3:\$A\$22,TEXT(VLOOKUP(H3,\$E\$3:\$F\$13,2),"00000")&"*",\$B\$3:\$B\$22)

Replies
7
Views
189
Replies
3
Views
175
Replies
1
Views
494
Replies
3
Views
123
Replies
9
Views
737

1,203,534
Messages
6,055,947
Members
444,839
Latest member
laurajames

### 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