Sumproduct based on relative cell

Solved/Closed
Brian - Aug 19, 2015 at 12:38 PM
BrianP Posts 1 Registration date Wednesday August 19, 2015 Status Member Last seen August 19, 2015 - Aug 19, 2015 at 02:07 PM
Hello,

I can use sumproduct based on information contained in the same range of rows of a column adjacent to it, but I can't figure out how to do a variation of this:


How would I sumproduct based on text contained in another row and column.. (two columns to the right and four rows up of the cell to be summed)

2 replies

BrianP Posts 1 Registration date Wednesday August 19, 2015 Status Member Last seen August 19, 2015 1
Aug 19, 2015 at 02:07 PM


Above is an example.
With sumproduct I can sum up the two amounts for Central Auto Parts and JS Safety,based on the "Dollar Amount" text entry in column A.
I want to add another condition however, which is that column D has an "MBE" entry, but it's a cell entry two columns to the right and 4 rows up relative to each dollar amount
1
Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Aug 19, 2015 at 01:08 PM
Brian, Good afternoon.

No problems.

BUT...the range MUST have same size.

eg.: =SUMPRODUCT((A1:A10="a")*(C31:C40>35)*(X1044:X1053))

ALL of them have 10 CELLS.

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0