# SUMIFS and Wildcard

#### Simonc64

##### Board Regular
Hi all, Ive researched this problem here without an exact answer to my problem so oping you can help!

I have a s/sheet with the following........

Column A contains Job Titles

Column B contains Salary Cost

Column C contains Cost Centres

What I want to do is very simple ie sum the "salary cost" based on job title for specific cost centres - the problem is that several cost centres contain the same job title.

My approach was

=SUMIFS(\$B:\$B,\$A:\$A,\$A\$25,\$C:\$C,"*08*")

Thining that this would sum the costs for all job titles (A25) where the cost centre starts with 08 but it doesnt! Any help gratefully received.

Thanks

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

##### MrExcel MVP
Hi all, Ive researched this problem here without an exact answer to my problem so oping you can help!

I have a s/sheet with the following........

Column A contains Job Titles

Column B contains Salary Cost

Column C contains Cost Centres

What I want to do is very simple ie sum the "salary cost" based on job title for specific cost centres - the problem is that several cost centres contain the same job title.

My approach was

=SUMIFS(\$B:\$B,\$A:\$A,\$A\$25,\$C:\$C,"*08*")

Thining that this would sum the costs for all job titles (A25) where the cost centre starts with 08 but it doesnt! Any help gratefully received.

Thanks

Care to post a few entries from column C?

#### Simonc64

##### Board Regular

Quite simply column C would contain entries such as 08750, 11000, 12801 etc but all will contain "sales manager" as a job title - all I want to do is sum the salary costs by cost centre by job title, but use the wildcard to distinguish between cost centres

#### Simonc64

##### Board Regular

thanks also Dan - that site has been blocked by my webmaster so will check it out at home later.

#### JackDanIce

##### Well-known Member
As per Aladin, if you can post an example of your worksheet, would make it easier. As a guess (you may need to modify), try:
Rich (BB code):
``=SUMPRODUCT(--(LEFT(C:C,2)="08"),--(A:A=A1),--(B:B=B1))``
Bits in red you may need to change but see if that helps

#### Marcol

##### Well-known Member

I think your formula should work, provided that Column C contains text values i.e. '08750 not numbers such as 8750 formatted "00000" to display a leading zero.

If it is only the cells beginning 08 you need then use ...

=SUMIFS(\$B:\$B,\$A:\$A,\$E\$1,\$C:\$C,"08*")

Using "*08*" will count e.g. '09108 in your calcs.

Last edited:

##### MrExcel MVP

Quite simply column C would contain entries such as 08750, 11000, 12801 etc but all will contain "sales manager" as a job title - all I want to do is sum the salary costs by cost centre by job title, but use the wildcard to distinguish between cost centres

If 08750, etc. are text, the leading 0 is a real-estate text-digit, the formula you have should succeed...
Do you get wrong figures or just a 0 result?

#### Simonc64

##### Board Regular
If 08750, etc. are text, the leading 0 is a real-estate text-digit, the formula you have should succeed...
Do you get wrong figures or just a 0 result?

just a 0 result

#### Marcol

##### Well-known Member
Agreed. That was my conclusion in Post#8

If there are numbers, or indeed a mix of real numbers and numbers as strings this might be safer
Code:
``=SUMPRODUCT(\$B\$2:\$B\$20*(\$A\$2:\$A\$20=[COLOR=#ff0000]\$F\$1[/COLOR])*(INT(\$D\$2:\$D\$20/1000)=8))``

Replace \$F\$1 with \$A\$25, similarly with \$E\$1 in Post #8

Don't apply SUMPRODUCT() to a whole Column, it will fail in 2003 and perform over a million unnecessary calculations in 2007 and above.
Always limit the range size, I prefer to use Dynamic Named Ranges where possible whenever I use this function

Last edited:

Replies
1
Views
279
Replies
3
Views
70
Replies
2
Views
65
Replies
3
Views
123
Replies
1
Views
89

1,109,408
Messages
5,528,603
Members
409,827
Latest member
Tmcgrew05

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...