sumproduct formula
 

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.

sumproduct formula
 

Try:

=SUMPRODUCT(--('Vend Sls Act'!B5:IV5=Worksheet!C8),--(,'Vend Sls
Act'!B5:IV5<=Worksheet!D8),'Vend Sls Act'!$B$7:$IV$7)


"Stacey" wrote:

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.

sumproduct formula
 

Hi Stacey,

Try it like this

=SUMPRODUCT(--(('Vend Sls Act'!B5:IV5=Worksheet!C8)*('Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

--
Hope that helps.

Vergel Adriano


"Stacey" wrote:

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.

sumproduct formula
 

Thank you. I got a #Ref as a result so I will keep trying. Thanks for the
idea.

"Vergel Adriano" wrote:

Hi Stacey,

Try it like this

=SUMPRODUCT(--(('Vend Sls Act'!B5:IV5=Worksheet!C8)*('Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

--
Hope that helps.

Vergel Adriano


"Stacey" wrote:

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.

sumproduct formula
 

Thank you. I tried it an I get a #Ref formula so I will keep trying it.
What does the -- mean in the formula?

"Toppers" wrote:

Try:

=SUMPRODUCT(--('Vend Sls Act'!B5:IV5=Worksheet!C8),--(,'Vend Sls
Act'!B5:IV5<=Worksheet!D8),'Vend Sls Act'!$B$7:$IV$7)


"Stacey" wrote:

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.

sumproduct formula
 

See

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
Stacey wrote:


What does the -- mean in the formula?

sumproduct formula
 

Yours worked. Thank you.

"Toppers" wrote:

Try:

=SUMPRODUCT(--('Vend Sls Act'!B5:IV5=Worksheet!C8),--(,'Vend Sls
Act'!B5:IV5<=Worksheet!D8),'Vend Sls Act'!$B$7:$IV$7)


"Stacey" wrote:

I am so close, I can't stand it. I have the following formula:
SUMPRODUCT(--(AND('Vend Sls Act'!B5:IV5=Worksheet!C8,'Vend Sls
Act'!B5:IV5<=Worksheet!D8)),'Vend Sls Act'!$B$7:$IV$7)

The layout is:
1. A worksheet where the billing days are across the top B5:IV5 and the
sales go across by day B7:IV7
2. I have a worksheet that gives me beginning billing days and ending
billing days.

If I take out the and from the above and just leave the = portion, the
formula works. Once I put the and statement in, it doesn't. How can I get
it to work between a range of cells? I hope this is clear. Thank you.