Sumproductto a variable row.

[Brian]

Here is what I am trying to do. From a list I am using an advance
filter to filter out all the sales of a salesperson. I then want to
add up what they have to be paid, from the commission column, provided
the status column = "Release"

The filter has to show all their sales, irrespective of the status of
the sale, so the salesman can see that all sales are accounted for.

As the number of rows in the advanced filter will be variable, I have
a problem with the sumproduct formula. I have tried using the range
as P8:Counta(P81000) in the sumproduct formula but obviously my
ignorance is creating the wrong result. How should this formula be
written.

Any assistance will be appreciated

 

[Jonathan Rynd]

(e-mail address removed) (Brian) wrote in @posting.google.com:

I have tried using the range
as P8:Counta(P81000)

Sounds like you want something like
OFFSET(P8,0,0,counta(p81000),1)

 

[Brian]

Thank you for your very prompt reply to my question but I don't quite
see how this will work. Let me show you exactly what I want to do. I
run an advanced filter to filter out all the sales for a particular
salesman. Column AQ of the filtered data holds the number of months
since a sale has been made. Commissions are paid in month 0, month 7
and month 13. From this filter I need a formula something like
Sumproduct((AQ8:AQ1000=7)*AX8:AX1000). The problem is that advanced
filter will result in a range of variable length. It might only be 10
lines, so I need to be able to specify a range that will end at line
10 in such a case, otherwise the sumproduct formula does not work as
there are empty cells.

Does this make sense?

 

[Jonathan Rynd]

(e-mail address removed) (Brian) wrote in

From this filter I need a formula something like
Sumproduct((AQ8:AQ1000=7)*AX8:AX1000).

Instead of using AQ8:AQ1000, you use
OFFSET(AQ8,0,0,counta(AQ8:AQ1000),1)

this form of OFFSET returns a variable-sized range depending on how many
elements of the range are filled.

 

[Brian]

Sorry, maybe I should be at the beginners section for this. Should my
formula now look like this?
=SUMPRODUCT((OFFSET(AY8,0,0,COUNTA(AY8:AY1000),1)=0)*OFFSET(AX8,0,0,COUNTA(AX8:AX1000),1)
Where column AY is the number of months since the sale was made and
column AX is the commission earned (obviously in the filtered data)
Using this I get a #Value answer. Both columns have numerical
figures.

 

[Jonathan Rynd]

(e-mail address removed) (Brian) wrote in

=SUMPRODUCT((OFFSET(AY8,0,0,COUNTA(AY8:AY1000),1)=0)*OFFSET(AX8,0,0,COU
NTA(AX8:AX1000),1)

Use COUNTA to count how many text values there are. Use COUNT to count how
many numeric values there are.

Here's my formula:
=SUMPRODUCT(--(OFFSET(AY8,0,0,COUNTA(AY8:AY1000),1)=0),OFFSET
(AX8,0,0,COUNTA(AX8:AX1000),1))

You must make sure that no cells in either list are blank, and that the
number of cells in each is the same. It works for me.

Send me your spreadsheet if you are having problems.

 

[Jonathan Rynd]

Use COUNTA to count how many text values there are. Use COUNT to
count how many numeric values there are.

sorry, that sentence was completely irrelevant to the problem.