Expanded SUMIF question

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

Forum statistics

Threads
1,137,335
Messages
5,680,893
Members
419,937
Latest member
Talic

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