• 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.

SUMPRODUCT

P

phillygirl777

Member
This has been driving me crazy. I know there is something minor and stupid that my eye just cannot catch as I've been staring at it for two long. I am wondering what I have done wrong.


=SUMPRODUCT(('[Total Technology Nov.xlsx]Slide 6'!$D$4:$K$4=D$27)*('[Total Technology Nov.xlsx]Slide 6'!$B$6:$B$20=$B33))*('[Total Technology Nov.xlsx]Slide 6'!$D$6:$K$20)


Your input is greatly appreciated.
 
Hi, phillygirl777!

I think it's just a parenthesis, try this:

=SUMPRODUCT(('[Total Technology Nov.xlsx]Slide 6'!$D$4:$K$4=D$27)*('[Total Technology Nov.xlsx]Slide 6'!$B$6:$B$20=$B33)*('[Total Technology Nov.xlsx]Slide 6'!$D$6:$K$20))

Regards!
 
Hi ,


What is the result you are expecting ?


What the formula is doing is :


Let us assume D4 = D27 ; since D4 is the first element of the vector D4:K4 , the first element of B6:B20 should be equal to B33 ; if this is so , then the formula will return the first element of the table D6:K20.


If you are getting 0 as the result , it may be that the corresponding entries are not matches , in which case the AND of the two conditionals will return a FALSE result.


Narayan
 
Hi, phillygirl777!

Download this file, it's the one which I used to comprobate the result before writing to you. I'm sure it works, in my example with XX in B33 and YYY in D27 it displays 49. I've painted the related cells in order to be easier to check.

http://www.2shared.com/file/pbutJmTi/SUMPRODUCT__for_phillygirl777_.html

Regards!
 
It works! thanks SirJB7. I enhanced the area with conditional formatting with


=AND($B6=$B$33,D$4=$D$27)


and two simple one on the header row and column.
 
You must log in or register to reply here.
Back
Top