vlookup and countif???

[Joe]

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

[Aladin Akyurek]

=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
>
>

[Joe]

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

[Dav]

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

[Joe]

=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
>
>

[Dav]

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