# SumProduct: Find value at intersection

#### gheyman

##### Well-known Member
I have Peoples Names down A starting in Row 2 down to 10 and Product ID in Row 1 starting in B thru H. My Values are in B2:H10

SumProduct((Indirect("SHeet1!\$A\$2:\$A\$10")=A1)*(Indirect("SHeet1!\$B\$1:\$H\$1")=B1)*(Indirect("SHeet1!\$B\$2:\$H\$10"))

Gives me an error

What am I doing wrong?

Thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Joe4

I don't think you are using it SUMPRODUCT properly. All the ranges need to be the same size.
Range1: A2:A10 - 9 cells
Range2: B1:H1 - 7 cells
Range3: B2:H10 - 63 cells

See: https://exceljet.net/excel-functions/excel-sumproduct-function

I think you will be better off by showing us some data, and simply explain what it is you are trying to do with it.

Last edited:

#### gheyman

##### Well-known Member
Thanks

I have Employee names down Column A and Product type across Row 1
In the intersections I have how many of each product the employee sold.

Name Product1 Product2
John 1 10
Jill 5 3
John 7 0

I need a formula that will show me how many of Product 1 John sold.

#### Joe4

Multiple ways to accomplish something like that, such as:
1. SUMIF (or SUMIFS) function
2. Pivot Table
3. Subtotals

Which option works best for you is partly determined about how/where you want these values to appear.
So, if were just looking up a specific person, SUMIF would probably work.
But if you wanted a summary of everyone, you would probably want to look at using options 2 or 3.
There are lots of good tutorials on using Pivot Tables out there (I prefer the YouTube ones myself, as I am a visual person).

#### gheyman

##### Well-known Member

Thanks. Didn't know you could use SumIfs with the criteria running down columns and also running across rows.

Pivot will not work for what I need.

Thanks!

Last edited:

#### Joe4

Like I said, use of SUMIF/SUMIFS is more likely to be used when looking up a specific instance (and not trying to summarize all at once).
Quite frankly, I think it is much easier to do this sort of thing with Access than Excel, using Queries.

A lot will depend exactly what you are trying to do and how.
Are you trying to summarize all data?
Are you trying to lookup a single value (if so, where is the "criteria" coming from)?
And are you trying to return the result(s)?

Last edited:

#### gheyman

##### Well-known Member

I have used the Sumproduct before and tried to convert it to what I need now with no luck
=SUMPRODUCT((INDIRECT(B2:B20)=B44)*(INDIRECT(C1:S1)=C44)*INDIRECT(C2:S20))
where B44 = Johny and C44 = Product A
B2:B20 is a list of Employee Names
C1:S1 is the Products ("Product A", "Product B"....)

I have a lot of data and need to grab certain information.

I have employees down a column and product across the top row. in each cell I have how many of each product the employee sold.
So if Johny is an employee (whos name is in the column a) and he sold 10 of Product A I need a formula where I can search for "Johny" and "Product A" and the results gives me 10.

I have used SumProducts for this type of thing before but form some reason I cannot get it to work now

#### Joe4

I have used SumProducts for this type of thing before but form some reason I cannot get it to work now
Can you provide one of those "working" SUMPRODUCT formulas you have (or if it was done on here, a link to it)?
I would like to see a working SUMPRODUCT formula where the ranges are not the same size - maybe it is some technique that I am unaware of, but I would need to see what a working one looks like to see how it works.

#### gheyman

##### Well-known Member
An Index with two Match formula gets me what I need in this instance.

But here is the formula I have in another workbook where I used the Sumproduct. This formula works

=SUMPRODUCT((INDIRECT("Cost_Data!\$A\$4:\$A\$1000")=\$C\$3)*(INDIRECT("Cost_Data!\$D\$4:\$D\$1000")=\$D6)*(INDIRECT("Cost_Data!\$I\$3:\$O\$3")=I\$4)*INDIRECT("Cost_Data!\$I\$4:\$O\$1000"))

#### Joyner

##### Well-known Member
Pivot will not work for what I need. [/QUOTE said:
Why won't a Pivot Table Work? PTs are made to slice and dice the data.

From what you describe you would first need to Unpivot the data, easy if you have Power Query then you can get to any data you want.

Replies
2
Views
54
Replies
1
Views
107
Replies
5
Views
71
Replies
5
Views
66
Replies
2
Views
103

1,109,341
Messages
5,528,144
Members
409,802
Latest member
joeino

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