• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

nesting sumproduct with posible blank values....

foster

New Member
New here and hoping for help.

I have two column data "D6:D39" with 15 fixed dropdown choices and "E6:E39" which has dates (mm/dd/yy) users enter.

I only want excel to give me the total of the "D" range IF it has a date beside it. I have tried various formulas but all examples seem to require specifics ie =1 <>1

example: D has cells 6 thru 10 with "Apple" 11,12,15 "Peach" and E has a used defined date in cells E 6,7,11,15 it should answer 4 (I don't care in this column how many apples or peahes) just how many have dates associated.


Hope someone can help, I've spent a couple days on this and still get the wrong answer...


Thank you in advance.
 
Foster


try this


=+SUMPRODUCT((1*($D$6:$D$39<>"")*($E$6:$E$39<>"")))


if you want to select a piece of fruit change to


=+SUMPRODUCT((1*($D$6:$D$39="Peach")*($E$6:$E$39<>"")))

=+SUMPRODUCT((1*($D$6:$D$39=$G$30)*($E$6:$E$39<>""))) ' where G30 has a Fruit name

etc
 
Back
Top