vlookup and countif???
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 |
vlookup and countif???
=SUMPRODUCT(--($A$2:$A$100="Main"),--($B$2:$B$100="Yes"))
Joe wrote: 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 |
vlookup and countif???
Hello
I have tried to us this formula but it returns a #NUM! value. I am trying to count no sum the cells Thanks -- Cheers "Aladin Akyurek" wrote: =SUMPRODUCT(--($A$2:$A$100="Main"),--($B$2:$B$100="Yes")) Joe wrote: 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 |
vlookup and countif???
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 |
vlookup and countif???
=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 |
vlookup and countif???
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 |
All times are GMT +1. The time now is 07:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com