=SUMPRODUCT(('Next Ref No - 1st Qtr'!E:E="main")*('Next Ref No - 1st
Qtr'!K:K="yes"))

thank you so much for your response, i am now putting in the above formula
but i am still getting #NUM!

What am i doing wrong?

thanks
--
Cheers


"Dav" wrote:


Only a slight ammendment
=SUMPRODUCT(($A$2:$A$100="Main")*($B$2:$B$100="Yes "))

I have enclosed the yes in " but it displays with a space above, it
should not be there! just "yes"

The conditions return a list of true and false, if they are multiplied
together you get
true x true =1
true x false =0
false x true =0
false x false=0

So it only sums the values when its true

The original formula tries to sum text and is not happy hence #num

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=520293

Sumproduct does not work with column references, If your first row is
column headings replace.

E:E with e2:e65536 should your range need to be this long, an similarly
for K:K

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=520293