SUMPRODUCT function

[Randerson]

Hello everybody,

I have the following problem:
I have a big data base printed in one spreadsheet from the company that I work. In it there are many informations organized in columns as geographic sector, local of work, work priority, specialist required, and others.
The colunm specialist required has the names: MAJUST01, MAJUST02, MAJUST03, ..., MAJUST10 from the specialty adustment, MSOLDA01, MSOLDA02, ..., MSOLDA10 from the specialty welding, and it goes with the same parttern on 8 more specialty.

Using multiple criteria I want, for example, to count all performed work from one geographic sector, from one priority and from one specialty. The problem in that there are 10 different names for one specialty, as It is written above.

One workmate suggest the following solution.


step 1: create a shape column range for which specialty and filled them with the different names related with them. For example.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Welding[/TD]
[TD="align: center"]Adustment[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]MSOLDA01[/TD]
[TD="align: center"]MAJUST01[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]MSOLDA02[/TD]
[TD="align: center"]MAJUST02[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]MSOLDA10[/TD]
[TD="align: center"]MAJUST10[/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]

Step 2: step2: use the SUMPRODUCT function associated with SUMIFS function as following:

SUMPRODUCT(COUNTIFS(COLUMN_Geo_Work;"1";COLUMN_Prio_Work;"P2";COLUMN_Specialty;Range("A2:A11"))

Obs.: The size of the specialty columns is different to the size of the columns from the data base (it is much smaller)

Well, this code returns exactly what I want. The number of performed work from geographyc sector 1 from priority P2, and from welding specialty.

I tried this code:

SUMPRODUCT((COLUMN_Geo_Work="1")*(COLUMN_Prio_Work="P2")*(COLUMN_Specialty=Range("A2:A11"))

But it didn't work because, as I said before, the size on the specialty columns is different to the size of the columns from the data base.

So my question is: How does it work??? I know the basis of SUMPRODUCT and COUNTIFS but I can't understand what is happening here. I mean, how the first code is working and why my code is working if the first one it is.

Sorry for this long text.

since I already appreciate the help

 

[steve the fish]

In your first one the sumproduct is just adding the counts of the countifs formula when A2 is used, A3 is used all the way up to A11. The ranges used for the criteria ranges need to be the same length.

In the second one you are creating 3 arrays and multiplying them together then adding the products (A2*B2*C2 + A3*B3*C3 etc etc). For this reason the arrays need to be the same length.

 

[Randerson]

In your first one the sumproduct is just adding the counts of the countifs formula when A2 is used, A3 is used all the way up to A11. The ranges used for the criteria ranges need to be the same length.

I had thought in this answer. Well, thinking that the COUNTIFS would return an array, I had the idea to change the SUMPRODUCT just for SUM. With this I change was expecting to achieve the same result but It wasn't. In my conception, with the COUNTIFS had returned an array the function SUM should work.

 

[steve the fish]

You could use SUM if you enter it CNTL-SHIFT-ENTER.

 

[Randerson]

Thank you!!!