=COUNTIFS CALCULATION GIVING #VALUE!

[lostboyca]

I am trying to calculate a certain strings using =COUNTIFS(1:1,"TEST1",2:15,"PASS") which is giving me #VALUE! error. As the data goes on I will add more information after the D column and I want it to calculate automatically. Am I using the wrong function? So my expectation that it looks on column 1 for the word TEST1 if it sees the text I would like it to count the text PASS underneath it.

	A	B	C	D
1	TEST1	TEST2	TEST3	TEST4
2	PASS	FAIL	FAIL	FAIL
3	PASS	PASS	FAIL	PASS
4	FAIL	FAIL	PASS	FAIL
5	FAIL	PASS	FAIL	FAIL

 

[RoryA]

Your ranges need to be the same size/shape. Assuming the TEST items won't repeat, you could use:

=COUNTIF(INDEX($1:$1048576,0,MATCH("TEST1",1:1,0)),"PASS")

 

[lostboyca]

Worked like a charm thanks for the help

 

[lostboyca]

Your ranges need to be the same size/shape. Assuming the TEST items won't repeat, you could use:

=COUNTIF(INDEX($1:$1048576,0,MATCH("TEST1",1:1,0)),"PASS")

This almost worked when I did a copy to another section the calculation did not work. For example I copy Cell B - H to I - O and update the formula to point to the correct parameters it will not calculate. see attachment. I'm grabbing data from other worksheet for this as well. Weird this forum does not allow you to post the exact excel. One of the sample is =COUNTIF(INDEX(CAL_Default_SLP_Port9100_NetBio!$2:$500,0,MATCH($B$1,1:1,0)),$C$2). you can download the zip excel from http://www.lemongrass-bistro.com/INDEX_MATCH_CALCULATION.zip zipped excel

 

[RoryA]

If you're matching to a different sheet, you need to include that sheet name for the 1:1 part:

=COUNTIF(INDEX(CAL_Default_SLP_Port9100_NetBio!$2:$500,0,MATCH($I$1,CAL_Default_SLP_Port9100_NetBio!$1:$1,0)),$J$2)

 

[lostboyca]

awesome thanks taht worked