SumIF Dilemma Part 2

runningonempty

New Member
Joined
Aug 7, 2007
Messages
35
account code £££s
911112 aab 1000
912222 aaa 1500
911112 aab 2000
911112 aaa 2500
924664 acc 3000
955232 aca 3500
911112 acb 4000


formula =SUMPRODUCT(--(E4:E10=E18),--(F4:F10=F18),(G4:G10))
result 3500



criteria 955232 aca

Dilemma? How do I sumproduct using left function 2 place on code column?
I only want for eg account codes 911112 with codes starting with ac??
Criteria ??? 911112 ac



Please help, thanks, also how do I copy spreadsheet into message board so it looks just like i am seeing it on excel with rows and columns etc?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
If I understand correctly, try
=SUMPRODUCT(--(E4:E10=E18),--(LEFT(F4:F10,2)=F8),(G4:G10))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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