# Sumproduct or something else? (Excel 2003)

#### leeval

##### New Member
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?

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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:
Rich (BB code):
``````=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.

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"

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.

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

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.

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.

Replies
0
Views
159
Replies
8
Views
538
Replies
16
Views
210
Replies
12
Views
512
Replies
1
Views
144

1,196,079
Messages
6,013,311
Members
441,760
Latest member
Sharina

### 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.

### Which adblocker are you using?

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

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