Results 1 to 10 of 10

Squeezing more conditions into a sample SUMPRODUCT formula - show a novice pls!

Threaded View

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    helsinki
    MS-Off Ver
    Excel 2010
    Posts
    23

    Squeezing more conditions into a sample SUMPRODUCT formula - show a novice pls!

    Hi Wizards!

    I have the following formula:

    =SUMPRODUCT((NINOSyNINAS!D4:D5000="F")*(((NINOSyNINAS!GA4:GA5000<=TPRIII!A11)*(NINOSyNINAS!GA4:GA5000>=TPRIII!A10))+((NINOSyNINAS!GB4:GB5000<=TPRIII!A11)*(NINOSyNINAS!GB4:GB5000>=TPRIII!A10))))

    What I would need to do is to add the following condition to this formula:

    Only count IF

    NINOSyNINAS!BP5:BP5000 OR
    NINOSyNINAS!BQ5:BQ5000 OR
    NINOSyNINAS!BR5:BR5000 OR
    NINOSyNINAS!DG5:DG5000 OR
    NINOSyNINAS!DH5:DH5000 OR
    NINOSyNINAS!DI5:DI5000 OR
    NINOSyNINAS!EX5:EX5000 OR
    NINOSyNINAS!EY5:EY5000 OR
    NINOSyNINAS!EZ5:EZ5000 OR

    is not blank.

    I figured the new condition would look like this:

    ((NINOSyNINAS!BP:BP5000<>"")+(NINOSyNINAS!BQ:BQ5000<>"")+(NINOSyNINAS!DG:DG5000<>"")+(NINOSyNINAS!DH:DH5000<>"")+(NINOSyNINAS!DI:DI5000<>"")+(NINOSyNINAS!EX:EX5000<>"")+(NINOSyNINAS!EY:EY5000<>"")+(NINOSyNINAS!EZ:EZ5000<>""))

    But how do I fit it in the main formula?

    I tried:

    =SUMPRODUCT((NINOSyNINAS!D4:D5000="F")*(((NINOSyNINAS!GA4:GA5000<=TPRIII!A11)*(NINOSyNINAS!GA4:GA5000>=TPRIII!A10))+((NINOSyNINAS!GB4:GB5000<=TPRIII!A11)*(NINOSyNINAS!GB4:GB5000>=TPRIII!A10))))*((NINOSyNINAS!BP:BP5000<>"")+(NINOSyNINAS!BQ:BQ5000<>"")+(NINOSyNINAS!DG:DG5000<>"")+(NINOSyNINAS!DH:DH5000<>"")+(NINOSyNINAS!DI:DI5000<>"")+(NINOSyNINAS!EX:EX5000<>"")+(NINOSyNINAS!EY:EY5000<>"")+(NINOSyNINAS!EZ:EZ5000<>""))

    But it does not work...

    Thank you for your help!!!
    Last edited by RogerRangeRover; 11-30-2012 at 09:26 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1