SUMIFS and Wildcard

[Simonc64]

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

 

[JackDanIce]

Without seeing your spreadsheet, my guess is you need to use SUMPRODUCT. Have a read of: Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online
It gives a good example and explanation of how it works

 

[Aladin Akyurek]

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]

Hi Aladin

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]

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

 

[JackDanIce]

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:

=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]

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.

 

[Aladin Akyurek]

Hi Aladin

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]

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]

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

=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