sumproduct formula

[Stacey]

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.

 

[Toppers]

Try:

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

 

[Vergel Adriano]

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)

 

[Stacey]

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

 

[Stacey]

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

 

[JE McGimpsey]

See

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

 

[Stacey]

Yours worked. Thank you.

Try:

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