Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forget SUMIF, COUNTIF and VLOOKUP | Excel Worksheet Functions | |||
Countif + vLookup: Can they work together? | Excel Discussion (Misc queries) | |||
Countif or Vlookup | Excel Worksheet Functions | |||
Countif? or Vlookup? Lost?? | Excel Worksheet Functions | |||
vlookup, sumif, if, countif, help | Excel Worksheet Functions |