Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / January 2006

Tip: Looking for answers? Try searching our database.

Help with SUMPRODUCT?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
devbox - 27 Jan 2006 09:51 GMT
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

Signature

devbox

Reply to this Message
Bob Phillips - 27 Jan 2006 11:50 GMT
Try

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> Hi,
>
[quoted text clipped - 26 lines]
> devbox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20047
> View this thread: http://www.excelforum.com/showthread.php?threadid=505669
Reply to this Message
marcus@hotmail.com - 27 Jan 2006 15:41 GMT
>Try
>
>=SUMPRODUCT(--(A5:A100>=F6),--(A5:A100<G6),--(A5:A100<>""),B5:B100)

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

Marcus
Reply to this Message
Dave Peterson - 27 Jan 2006 15:49 GMT
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

> >Try
> >
[quoted text clipped - 4 lines]
>
> Marcus

Signature

Dave Peterson

Reply to this Message
devbox - 28 Jan 2006 11:29 GMT
Working fine!

Big thanks! :)

Signature

devbox

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.