Sumif

J

jlkirk

Active Member
Joined
May 6, 2002
Messages
328
Office Version
  1. 365
In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG". Any ideas?
Thanks in advance.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Barry,
A little different twist: Assume I wanted to sum all of those cells that have "ABC" and "DEFG" to another cell on another worksheet? I tried the following, but it didn't do the trick: "SUMIF(A1:A10,OR(LEFT(A1,3)="ABC",LEFT(A1,4)="DEFG"),B1:B10)".
Any ideas?
Thanks again.
 
Upvote 0
Try this:
=SUM(SUMIF(A1:A10,{"ABC","DEFG"}&"*",B1:B10))
 
Upvote 0
Rory,
Yes, that works great! Mind dissecting the formula and telling me how it does its thing?
Thanks.
 
Upvote 0
Sure - by using the array constant, you effectively get 2 separate SUMIFs:
=SUMIF(A1:A10,"ABC*",B1:B10)
and
=SUMIF(A1:A10,"DEFG*",B1:B10)
where the * acts as a wildcard so the cell only has to start with either piece of text, not be an exact match.
The exterior SUM formula then adds the results of the two SUMIF formulas to get a combined total.
Does that make sense?
 
Upvote 0
Rory,
One final question: Can you use your formula within a sumproduct function? That is, assume the same facts as I presented to you before, with the addition that Column D rows 1-10 contain various contract numbers. I would like to sum all of those values in Column B that contain the text as you solved for before, but only if the respective cells in Column D contains the number "111". Any ideas?
Thanks again for all of your help.
 
Upvote 0
=SUMPRODUCT((D1:D10=111)*((LEFT(A1:A10,3)="ABC")+(LEFT(A1:A10,4)="DEFG"))*B1:B10)
should work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Calculating Multiple Columns with SUMIFS
Replies
3
Views
184
Joe4
Joe4
V
  • Question
VBA: Clear Contents for Multiple Groups of Columns based on Data
Replies
3
Views
257
VBA Beginner 2024
V
E
  • Question
Interest Due Log
Replies
6
Views
116
ExcelNew7
E
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
171
B-radK
B
C
  • Question
Distribute actuals against planned values
Replies
0
Views
157
chullan88
C

Forum statistics

Threads
1,214,670
Messages
6,120,830
Members
448,990
Latest member
rohitsomani

Share this page

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