SumProduct and row lookups
 

I have a worksheet that is looking up data in other worksheets and summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to incorporate a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the vendor name
on the main page and returns the summed up data from the reference page. Any
suggestions? Thank you

SumProduct and row lookups
 

Can you explain that with a data example, it seems to already be doing that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to incorporate a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the vendor
name
on the main page and returns the summed up data from the reference page.
Any
suggestions? Thank you

SumProduct and row lookups
 

What you have to do is know the exact row of the data that you need returned.
In the workbook I have a tab of data with plan numbers for each supplier by
day. The report tab wants a monthly summary of those plan numbers. So what
I want it to do is to lookup the vendor name on the report tab and pull the
monthly numbers from the data tab for that vendor by looking up its name.
Right now I have to see that the vendor's data is on row 19, which is what is
reflected in the formula.

"Bob Phillips" wrote:

Can you explain that with a data example, it seems to already be doing that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to incorporate a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the vendor
name
on the main page and returns the summed up data from the reference page.
Any
suggestions? Thank you

SumProduct and row lookups
 

Is this what you want?

=SUMPRODUCT(('Vend Sls Act'!$A$19:$A$25=Worksheet!$B$8)*('Vend Sls
Act'!$B$5:$IV$5=Worksheet!$C$8)*('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8)*('Vend Sls Act'!$B19:$IV25))

I have assumed that the vendor is in Worksheet!B8, and the data is in row
19:25, with the vendor name in column A

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Stacey" wrote in message
...
What you have to do is know the exact row of the data that you need
returned.
In the workbook I have a tab of data with plan numbers for each supplier
by
day. The report tab wants a monthly summary of those plan numbers. So
what
I want it to do is to lookup the vendor name on the report tab and pull
the
monthly numbers from the data tab for that vendor by looking up its name.
Right now I have to see that the vendor's data is on row 19, which is what
is
reflected in the formula.

"Bob Phillips" wrote:

Can you explain that with a data example, it seems to already be doing
that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and
summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to incorporate
a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the vendor
name
on the main page and returns the summed up data from the reference
page.
Any
suggestions? Thank you

SumProduct and row lookups
 

Thank you. What does the * do?

I think this may be close but I think I should explain a little better.

Vend Sls Act B5:IV5 contains the each individual billing day of the year
Worksheet C8 & D8 contains the start and end dates to look up

Vend Sls Act B19:IV19 is the actual row that the vendor is on.

In summary, it takes the date range above and adds up the data on row 19
that relates (almost like an hlookup).

What I am trying to do is make row 19 a variable that looks up a value from
the report tab.


Thank you so much for your patience and your help with this. Maybe if I
knew what the * does, I can modify your formula to do the above. Thanks
again!


"Bob Phillips" wrote:

Is this what you want?

=SUMPRODUCT(('Vend Sls Act'!$A$19:$A$25=Worksheet!$B$8)*('Vend Sls
Act'!$B$5:$IV$5=Worksheet!$C$8)*('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8)*('Vend Sls Act'!$B19:$IV25))

I have assumed that the vendor is in Worksheet!B8, and the data is in row
19:25, with the vendor name in column A

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Stacey" wrote in message
...
What you have to do is know the exact row of the data that you need
returned.
In the workbook I have a tab of data with plan numbers for each supplier
by
day. The report tab wants a monthly summary of those plan numbers. So
what
I want it to do is to lookup the vendor name on the report tab and pull
the
monthly numbers from the data tab for that vendor by looking up its name.
Right now I have to see that the vendor's data is on row 19, which is what
is
reflected in the formula.

"Bob Phillips" wrote:

Can you explain that with a data example, it seems to already be doing
that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and
summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to incorporate
a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the vendor
name
on the main page and returns the summed up data from the reference
page.
Any
suggestions? Thank you

SumProduct and row lookups
 

The * does the same as the --, it coerces the TRUE/FALSE arrays to 1/0
arrays. It is needed here as I am using a 3D array, not just 1D.

All that you mentioned, I understood implicitly. I assumed the vendor data
was in 19:25, so my version pulls back the data from the row matching the
vendor in column A

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stacey" wrote in message
...
Thank you. What does the * do?

I think this may be close but I think I should explain a little better.

Vend Sls Act B5:IV5 contains the each individual billing day of the year
Worksheet C8 & D8 contains the start and end dates to look up

Vend Sls Act B19:IV19 is the actual row that the vendor is on.

In summary, it takes the date range above and adds up the data on row 19
that relates (almost like an hlookup).

What I am trying to do is make row 19 a variable that looks up a value
from
the report tab.


Thank you so much for your patience and your help with this. Maybe if I
knew what the * does, I can modify your formula to do the above. Thanks
again!


"Bob Phillips" wrote:

Is this what you want?

=SUMPRODUCT(('Vend Sls Act'!$A$19:$A$25=Worksheet!$B$8)*('Vend Sls
Act'!$B$5:$IV$5=Worksheet!$C$8)*('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8)*('Vend Sls Act'!$B19:$IV25))

I have assumed that the vendor is in Worksheet!B8, and the data is in row
19:25, with the vendor name in column A

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Stacey" wrote in message
...
What you have to do is know the exact row of the data that you need
returned.
In the workbook I have a tab of data with plan numbers for each
supplier
by
day. The report tab wants a monthly summary of those plan numbers. So
what
I want it to do is to lookup the vendor name on the report tab and pull
the
monthly numbers from the data tab for that vendor by looking up its
name.
Right now I have to see that the vendor's data is on row 19, which is
what
is
reflected in the formula.

"Bob Phillips" wrote:

Can you explain that with a data example, it seems to already be doing
that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and
summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend
Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to
incorporate
a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the
vendor
name
on the main page and returns the summed up data from the reference
page.
Any
suggestions? Thank you

SumProduct and row lookups
 

That is awesome. I tried it and it works perfectly...Thank you Thank you
Thank you. This is HUGE!

"Bob Phillips" wrote:

The * does the same as the --, it coerces the TRUE/FALSE arrays to 1/0
arrays. It is needed here as I am using a 3D array, not just 1D.

All that you mentioned, I understood implicitly. I assumed the vendor data
was in 19:25, so my version pulls back the data from the row matching the
vendor in column A

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Stacey" wrote in message
...
Thank you. What does the * do?

I think this may be close but I think I should explain a little better.

Vend Sls Act B5:IV5 contains the each individual billing day of the year
Worksheet C8 & D8 contains the start and end dates to look up

Vend Sls Act B19:IV19 is the actual row that the vendor is on.

In summary, it takes the date range above and adds up the data on row 19
that relates (almost like an hlookup).

What I am trying to do is make row 19 a variable that looks up a value
from
the report tab.


Thank you so much for your patience and your help with this. Maybe if I
knew what the * does, I can modify your formula to do the above. Thanks
again!


"Bob Phillips" wrote:

Is this what you want?

=SUMPRODUCT(('Vend Sls Act'!$A$19:$A$25=Worksheet!$B$8)*('Vend Sls
Act'!$B$5:$IV$5=Worksheet!$C$8)*('Vend Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8)*('Vend Sls Act'!$B19:$IV25))

I have assumed that the vendor is in Worksheet!B8, and the data is in row
19:25, with the vendor name in column A

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Stacey" wrote in message
...
What you have to do is know the exact row of the data that you need
returned.
In the workbook I have a tab of data with plan numbers for each
supplier
by
day. The report tab wants a monthly summary of those plan numbers. So
what
I want it to do is to lookup the vendor name on the report tab and pull
the
monthly numbers from the data tab for that vendor by looking up its
name.
Right now I have to see that the vendor's data is on row 19, which is
what
is
reflected in the formula.

"Bob Phillips" wrote:

Can you explain that with a data example, it seems to already be doing
that
to me.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Stacey" wrote in message
...
I have a worksheet that is looking up data in other worksheets and
summing
ranges by using the following formula:

SUMPRODUCT(--('Vend Sls Act'!$B$5:$IV$5=Worksheet!$C$8),--('Vend
Sls
Act'!$B$5:$IV$5<=Worksheet!$D$8),'Vend Sls Act'!$B19:$IV19)

This is working great, however, I would like to be able to
incorporate
a
lookup to where the 'Vend Sls Act'!$B19:$IV19 piece looks up the
vendor
name
on the main page and returns the summed up data from the reference
page.
Any
suggestions? Thank you