Sumproduct or something else? (Excel 2003)

[leeval]

Hi everyone,

I have come across a small problem which I could use a fresh pair of eyes on.

I am basically trying to use formula to populate a stats table with data from my list.

From my list I would like to count a figure of certain occurences in several columns.

e.g

Columns A to D:
Job, Job Code, Area, SLA Pass/Fail

I need to count the number of passes/fails for specific job types in my list which contain certain area codes. And then populate a pre-designed table with the relevant info.

I have tried =sumproduct((Jobcode="__")*(Area="__")*(Pass/Fail="pass"))
which gives me a result of 0 despite there being dozens of occasions where my criteria is met.

I would appreciate it if someone could help me out with where I am going wrong. Is the function specific for numbers or something?

 

[Aladin Akyurek]

Hi everyone,

I have come across a small problem which I could use a fresh pair of eyes on.

I am basically trying to use formula to populate a stats table with data from my list.

From my list I would like to count a figure of certain occurences in several columns.

e.g

Columns A to D:
Job, Job Code, Area, SLA Pass/Fail

I need to count the number of passes/fails for specific job types in my list which contain certain area codes. And then populate a pre-designed table with the relevant info.

I have tried =sumproduct((Jobcode="__")*(Area="__")*(Pass/Fail="pass"))
which gives me a result of 0 despite there being dozens of occasions where my criteria is met.

I would appreciate it if someone could help me out with where I am going wrong. Is the function specific for numbers or something?

The following should work:

=SUMPRODUCT(
    --(JobCodeRange=JobCode),
    --(AreaRange=Area),
    --(PassFailRange="pass"))
 
=COUNTIFS(
    JobCodeRange,JobCode,
    AreaRange,Area,
    PassFailRange,"pass")

Note. The formula with COUNTIFS is valid on Excel 2007 and later.

 

[AlphaFrog]

The syntax of your Sumproduct formula looks good.

If your job codes are all numeric, don't surround the job code in the formula with quotes; "123" doesn't equal 123

Make sure the data doesn't have a trailing space; "test " doesn't equal "test"

 

[leeval]

Aladin, many thanks. That works perfectly. That is going to save me a hell of a lot of time.

The syntax of your Sumproduct formula looks good.

If your job codes are all numeric, don't surround the job code in the formula with quotes; "123" doesn't equal 123

Make sure the data doesn't have a trailing space; "test " doesn't equal "test"

The job codes are a mixture of characters, so would probably need quotes. Some of the data I need to include are numeric though.

Thank you both for your sound advice, much appreciated.

 

[leeval]

Also, would it be possible for a brief explanation of the -- part if you have time? Never come across that before.

 

[Aladin Akyurek]

Aladin, many thanks. That works perfectly. That is going to save me a hell of a lot of time.



The job codes are a mixture of characters, so would probably need quotes. Some of the data I need to include are numeric though.

Thank you both for your sound advice, much appreciated.

You are welcome.

If you want to use a job code directly in the formula and such codes are text value, it must be indeed put between a pair of double quotes. But if have it in a cell of its own, the formula can refer to that cell.

Also, would it be possible for a brief explanation of the -- part if you have time? Never come across that before.

Expressions like

JobCodeRange=JobCode

evaluates to a set of TRUE/FALSE values; the double minus is used to convert such values to their numeric equivalents in Excel, that is, TRUE to 1 and FALSE to 0.

See also:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=70547