software

help I am trying to write a formula that says the following.

if coloum a = main and if coloum b = yes please count the yes's

I have tried using vlookup and count if but I just get a null value returned

hope there is somebody there who can help

thanks
=SUMPRODUCT(--($A$2:$A$100=quot;Mainquot;),--($B$2:$B$100=quot;Yesquot;))

Joe wrote:
gt; help I am trying to write a formula that says the following.
gt;
gt; if coloum a = main and if coloum b = yes please count the yes's
gt;
gt; I have tried using vlookup and count if but I just get a null value returned
gt;
gt; hope there is somebody there who can help
gt;
gt; thanks
gt;
gt;

Hello

I have tried to us this formula but it returns a #NUM! value.

I am trying to count no sum the cells

Thanks
--
Cheersquot;Aladin Akyurekquot; wrote:

gt; =SUMPRODUCT(--($A$2:$A$100=quot;Mainquot;),--($B$2:$B$100=quot;Yesquot;))
gt;
gt; Joe wrote:
gt; gt; help I am trying to write a formula that says the following.
gt; gt;
gt; gt; if coloum a = main and if coloum b = yes please count the yes's
gt; gt;
gt; gt; I have tried using vlookup and count if but I just get a null value returned
gt; gt;
gt; gt; hope there is somebody there who can help
gt; gt;
gt; gt; thanks
gt; gt;
gt; gt;
gt;


Only a slight ammendment
=SUMPRODUCT(($A$2:$A$100=quot;Mainquot;)*($B$2:$B$100=quot;Yes quot;))

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

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: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=520293=SUMPRODUCT(('Next Ref No - 1st Qtr'!E:E=quot;mainquot;)*('Next Ref No - 1st
Qtr'!K:K=quot;yesquot;))

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
--
Cheersquot;Davquot; wrote:

gt;
gt; Only a slight ammendment
gt; =SUMPRODUCT(($A$2:$A$100=quot;Mainquot;)*($B$2:$B$100=quot;Yes quot;))
gt;
gt; I have enclosed the yes in quot; but it displays with a space above, it
gt; should not be there! just quot;yesquot;
gt;
gt; The conditions return a list of true and false, if they are multiplied
gt; together you get
gt; true x true =1
gt; true x false =0
gt; false x true =0
gt; false x false=0
gt;
gt; So it only sums the values when its true
gt;
gt; The original formula tries to sum text and is not happy hence #num
gt;
gt; Regards
gt;
gt; Dav
gt;
gt;
gt; --
gt; Dav
gt; ------------------------------------------------------------------------
gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; View this thread: www.excelforum.com/showthread...hreadid=520293
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=520293