COUNTIF and VLOOKUP combination?

[chuckles1066]

Apologies if this one's been covered, I couldn't find it.

Sheet1, cell A2 contains a person's name

Sheet2, column A will also contain that person's name (somewhere). I need to be able to count which columns from B-G contain a value greater than 0. So it seems (to me) to be a combination of COUNTIF and VLOOKUP except I can't get the syntax right.

I've messed about with SUMPRODUCT to no avail, I think I might even have dabbled with INDEX, MATCH and OFFSET as well! So I HAVE tried before asking

Thanks in advance.

 

[CWatts]

This should work for you:

=COUNTIF(OFFSET(Sheet2!$B$1:$G$1,MATCH(B2,Sheet2!$A:$A,0)-1,0),">"&0)

 

[chuckles1066]

Brilliant, thank you very much.

I got a circular reference error and then I changed the B2 after the MATCH statement to A2 and it works a treat!

 

[T. Valko]

Apologies if this one's been covered, I couldn't find it.

Sheet1, cell A2 contains a person's name

Sheet2, column A will also contain that person's name (somewhere). I need to be able to count which columns from B-G contain a value greater than 0. So it seems (to me) to be a combination of COUNTIF and VLOOKUP except I can't get the syntax right.

I've messed about with SUMPRODUCT to no avail, I think I might even have dabbled with INDEX, MATCH and OFFSET as well! So I HAVE tried before asking

Thanks in advance.

Try this...

Assuming you enter the formula in Sheet1 cell B2.

=COUNTIF(INDEX(Sheet2!B2:G15,MATCH(A2,Sheet2!A2:A15,0),0),">0")

 

[Aladin Akyurek]

Apologies if this one's been covered, I couldn't find it.

Sheet1, cell A2 contains a person's name

Sheet2, column A will also contain that person's name (somewhere). I need to be able to count which columns from B-G contain a value greater than 0. So it seems (to me) to be a combination of COUNTIF and VLOOKUP except I can't get the syntax right.

I've messed about with SUMPRODUCT to no avail, I think I might even have dabbled with INDEX, MATCH and OFFSET as well! So I HAVE tried before asking

Thanks in advance.

Looks like...

B2, Sheet1, control+shift+enter, not just enter...

Either:

=SUM(IF(Sheet2!$A$2:$A$100=A2,(Sheet2!$B$2:$G$100>0)+0))

Or:

=SUM(IF(Sheet2!$A$2:$A$100=A2,IF(ISNUMBER(Sheet2!$B$2:$G$100),(Sheet2!$B$2:$G$100>0)+0)))

The latter can cope with text values that might occur in B:G.

 

[CWatts]

Brilliant, thank you very much.

I got a circular reference error and then I changed the B2 after the MATCH statement to A2 and it works a treat!

Ugh, sorry that was my bad in building, glad you got it though.