SumProduct: Find value at intersection

[gheyman]

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

 

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

 

[gheyman]

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]

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!

 

[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)?

 

[gheyman]

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]

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]

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.