Help with SUMPRODUCT?

[devbox]

Hi,

I need to total (count) all the pivot table entries displayed in A:C
where A is between the 30 minute intervals that I have in F and G per
colour listed in B.

I have tried
"=SUMPRODUCT((A5:A100>=F6),(A5:A100<G6),(A5:A100<>""),B5:B100)", but
only get 0.00.

Help?


INTERVAL ZONE
A B C D E F G H
GREEN
PIVOT TABLE
TIME ZONE Total 7:30 8:00
8:15 GREEN 1 8:00 8:30
8:19 YELLOW 1 8:30 9:00
8:19 YELLOW 1 9:00 9:30
8:20 YELLOW 1 9:30 10:00
8:21 YELLOW 1 10:00 10:30
8:22 YELLOW 1 10:30 11:00
8:22 GREEN 1 11:00 11:30

 

[Bob Phillips]

Try

=SUMPRODUCT(--(A5:A100>=F6),--(A5:A100<G6),--(A5:A100<>""),B5:B100)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[marcus]

Try

=SUMPRODUCT(--(A5:A100>=F6),--(A5:A100<G6),--(A5:A100<>""),B5:B100)

what those "--" mean?
what do they do?

Marcus

 

[Dave Peterson]

The first minus changes true to -1 (and false to 0). The second minus changes
-1 to 1 (and 0 to 0).

=sumproduct() likes to work with numbers, so this is a quick way to change those
boolean values to numbers.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[devbox]

Working fine!

Big thanks!