SUMIFS using a wild card

N

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
I have searched but can't find what I need.

With the below table:
Crew 5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 32px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32M</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32B</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32C</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">38.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">1.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">7.00</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have tried the formulas in row 26:
Crew 5

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">TRCSR11011</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">32</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">2615-C</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">ST</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Arial; FONT-SIZE: 8pt">10</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 8pt">38.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 8pt">9.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-FAMILY: Arial; FONT-SIZE: 8pt">0.00</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C26</TD><TD>=SUMIFS($C$9:$Q$9,$C$4:$Q$4,C21,$C$5:$Q$5,C22,$C$7:$Q$7,C24,$C$8:$Q$8,C25)</TD></TR><TR><TD>D26</TD><TD>=SUMIFS($C$9:$Q$9,$C$4:$Q$4,D21,$C$5:$Q$5,D22&"*",$C$7:$Q$7,D24,$C$8:$Q$8,D25)</TD></TR><TR><TD>E26</TD><TD>=SUMIFS($C$9:$Q$9,$C$4:$Q$4,E21,$C$5:$Q$5,E22,C5:Q5,E22&"*",$C$7:$Q$7,E24,$C$8:$Q$8,E25)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I actually only need one formula in C26 to account for all occurances of 32 regardless if it has a letter after or not. C26 & D26 returns correctly, but my attempt at E26 by putting in another Criterior is not right.

C26 should return 47.

I am doing this with 2007 but some users will be using 2003. That said, if I could get the help, I would appreciate a SUMIFS solution and a solution for 2003.

Thanks

EDIT: cells are formatted as General in Row 5
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
On all versions...
Code: 
=SUMPRODUCT(
   $C$9:$Q$9,
   --($C$4:$Q$4=C21),
   --ISNUMBER(SEARCH(C22,$C$5:$Q$5)),
   --($C$7:$Q$7=C24),
   --($C$8:$Q$8=C25))
 
Upvote 0
Sorry for the delay, had to drive home.

Thank you Both. Both formulas do the job.

Question - Is there a difference in fuctionality, speed, etc. between the two. I will eventually have this type formula in C26:Q26.

To top it off, now that I have the formula, I will try to adapt this in a macro for Worksheet_Deactivate or Activate Event since I need this to operate in the background (so to speak) so it doesn't show the formula in row 26, but just the results.

Alot of work for the macro. Just gathering info to implement, right now.
 
Upvote 0
Nalani said:
Sorry for the delay, had to drive home.

Thank you Both. Both formulas do the job.

Question - Is there a difference in fuctionality, speed, etc. between the two. I will eventually have this type formula in C26:Q26.

To top it off, now that I have the formula, I will try to adapt this in a macro for Worksheet_Deactivate or Activate Event since I need this to operate in the background (so to speak) so it doesn't show the formula in row 26, but just the results.

Alot of work for the macro. Just gathering info to implement, right now.
Click to expand...

You are welcome. Thanks for providing us feedback.

One set up has a lesser number of function calls, which might mean a slight advantage.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
R
  • Question Question
Find the value in a string using the corresponding phrase
Replies
1
Views
64
reznx
R
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
249
Cerka
C

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

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