.. I want the percentage of times that column C has a 1T, 1S, 1M, or 1L
and [col J] has a P* (any number or letter combo after it)...
Hazarding a guess, perhaps something like this ..
In sheet: 1st Down Dashboard,
Try in C3:
=SUMPRODUCT((ISNUMBER(SEARCH("P",'Game Play
Sheet'!J11:J100)))*(ISNUMBER(SEARCH(1,'Game Play
Sheet'!C11:C100))))/MIN(COUNTA('Game Play Sheet'!C11:C100),COUNTA('Game Play
Sheet'!J11:J100))
Format C3 as percentage (dp to taste)
The numerator SUMPRODUCT(...) returns the required counts satisfying
criteria in both cols C and J, while the denominator MIN(...) returns the
total "completed" plays in both cols C and P
(assuming "completed" plays are where both cols C and P contain inputs)
Replace SEARCH with FIND if you need it to be case sensitive (SEARCH is not
case sensitive). Adapt the ranges to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bmstar" wrote:
I have a very similar problem.
I have attached the document below. On page two, in cell C:3, I want
to be able to be able to show a percentage. My goal is to look at each
record in the Game Play Sheet and compare colums C and J. I want the
percentage of times that column C has a 1T, 1S, 1M, or 1L and J has a
P* (any number or letter combo after it)...
Make any sense? I've tried to use "Countif" with "Counta" and
SumProduct....a couple IF statements....but to no avail. One of my
biggest challenges is that when I try to search multiple records I
can't use a wild card in the search pattern...and it's driving me
nuts.... (i.e. Countif (!GamePlaySheet:C11:C79="1*") ... any help?
+-------------------------------------------------------------------+
|Filename: Test Page.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5014 |
+-------------------------------------------------------------------+
--
bmstar
------------------------------------------------------------------------
bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264
View this thread: http://www.excelforum.com/showthread...hreadid=560522