SumProduct: Find value at intersection

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,488
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 14, 2005
Messages
1,744
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 15, 2005
Messages
1,202
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top