How do I enter a wildcard in SUMPRODUCT? 1

[JBruyet]

Hey all,

I'm trying to figure out a way to count tbe number of names entered in a column. I can't seem to get COUNT to work to just give me a number. I've used the following formula:

=SUMPRODUCT(--ISNUMBER(FIND("Name",C3:C74)))

but it only counts the number of occurences for Name. Is there a way to enter a wildcard in this formula so I can get a total number for any namesentered? Is there another formula that will give me the number of names, any names, that have been entered?

Thanks for your help,

Jobee

 

[AnotherHiggins]

Is there another formula that will give me the number of names, any names, that have been entered?

Do you want to count all non-blank cells in a range?

Or are you after all entries that contain a particular string anywhere in them (Example: cheese, he, heat and the all contain the string, "he")?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JBruyet]

Actually, counting all non-blank cells in a range would work for us. We'll make sure to leave cells blank where we don't have a full name yet (first AND last name).

Thanks

Jobee 1

 

[SkipVought]

How about the COUNTA function?

Skip,

_[red][/red]

 

[JBruyet]

Hey Skip, that works. Is there a web site that lists the different Excel functions and how they work? This is the first I've seen of the COUNTA formula.

Thanks

Jobee 1

 

[SkipVought]

In Help check out Statistical Functions & Lookup & Reference Functions. Help has a wealth of information.

Skip,

_[red][/red]

 

[KenWright]

Think you've alreday gotten your answer, but just in case it helps, here is something i've posted previously:-

Wildcards can be used in various formulas but not all functions will support them. Another option that is similar is to use FIND or SEARCH within a SUMPRODUCT function, which will mimic a wildcard search.

=SUMPRODUCT(ISNUMBER(SEARCH("bcd",$A$2:$A$30))*$B$2:$B$30)

Other misc examples

=SUMIF(P25,"<>*LLC",DB2B5)
=COUNTIF(data,"SEC*")
=IF(COUNTIF(A2:A2,"?abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=COUNTIF(A1:A5,"*a*")
=COUNTIF(A1:A5,"*a")
=COUNTIF(A1:A5,"a*")
=COUNTIF(A1:A5,"a?cd")
=AVERAGE(IF(ISNUMBER(SEARCH("green",A1:A10)),B1:B10))
=COUNTIF(A:A,"????t*")
=SUMPRODUCT((F3:F5000="1c33")*ISNUMBER(SEARCH("3w15?",I3:I5000)))
=VLOOKUP("*"&A1&"*",Sheet2!A:B,2, FALSE)


Along with some that I don't think I've posted before, though not too sure to be honest - Some of the arguments here are just range names, showing you that they can be used quite easily as well:-

SumProduct - Examples of Syntax

=SUMPRODUCT(($A$1:$A$100="L")*(CODE($B$1:$B$100)=66))
=SUMPRODUCT(($A$1:$A$100="L")*(CODE(UPPER($B$1:$B$100))=66))
=SUMPRODUCT(($A$1:$A$100="L")*(LEFT($B$1:$B$100,1)="b"))
=SUMPRODUCT(($B$6:$B$14=E6)*($C$6:$C$14=F6)*($D$6:$D$14))
=SUMPRODUCT((A1:A100="apples")*(C1:C100="Spain")*(D1100))
=SUMPRODUCT((A1:A100>=$E$1)*(A1:A100<=$E$2)*(B1:B100=$E$3)*(C1:C100=$E$4)*D1
100)
=SUMPRODUCT((A1:A100>=1)*(A1:A100<=100)*A1:A100)/SUMPRODUCT((A1:A100>=1)*(A1
:A100<=100))
=SUMPRODUCT((A1:A20<0)*(B1:B20))/SUMPRODUCT((A1:A20<0)*1)
=SUMPRODUCT((A1:A20=13)*(B1:B20>6)*C1:C20)
=SUMPRODUCT((A1:A20=2003)*(B1:B20=1)*C1:C20)
=SUMPRODUCT((A1:A365>=DATEVALUE("10/01/2002")) *
(A1:A365<DATEVALUE("11/01/2002")) * (C1:C365))
=SUMPRODUCT((A1:A4="X")*(B1:B4="N")*C1:C4)
=SUMPRODUCT((A2:A100="red")+(A2:A100="blue")+(A2:A100="yellow"),B2:B100)
=SUMPRODUCT((A2:A100={"red","blue","yellow"})*B2:B100)
=SUMPRODUCT((A2:A105=D1)+(A2:A105=E1)+(A2:A105=F1),B2:B105)
=SUMPRODUCT((A2:A120={"red","blue","yellow"})*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B120))
=SUMPRODUCT((A2:A120=D1:F1)*(B2:B150))
=SUMPRODUCT((A2:B100="YourClass")*(B2:B100="YourSize")*C2:C100)
=SUMPRODUCT((A8:A200="Jan")*(B2:B10="Week 1")*H8:H200)
=SUMPRODUCT((A9:A25="blue")*ABS(C9:C25))
=SUMPRODUCT((B40:AE40=TRUE)*(B41:AE41="V"))
=SUMPRODUCT((dts>st)*(dts<=nd)*(B4:M4))
=SUMPRODUCT((EXACT(LEFT(data,1),"s")+0))
=SUMPRODUCT((LEFT(INDIRECT($B23&"!H2:H4500"),4)=C$3)*(INDIRECT($B23&"!F2:F45
00")=C$2),INDIRECT($B23&"!G2:G4500"))
=SUMPRODUCT((MOD(ROW(A1:A97),7)=0)*A1:A97)
=SUMPRODUCT((MOD(ROW(A1:A97),7)=1)*A1:A97)
=SUMPRODUCT((MOD(ROW(Range)-CELL("Row",Range)+H1,H2)=0)*(Range))
=SUMPRODUCT((MONTH(A1:A100)=6)*B1:B100)
=SUMPRODUCT((MONTH(A1:A300)=2)*(ISNUMBER(A1:A300)))
=SUMPRODUCT((MONTH(A1:A500)=1)*(B1:B500))
=SUMPRODUCT((MONTH(A8:A21)=MONTH(G3))*(B8:B21))
=SUMPRODUCT((MONTH(B2:B9)=11)*(A2:A9="Pending"))
=SUMPRODUCT((range1="L")*(LEFT(range2)="B"))
=SUMPRODUCT((Range1=A1)*(Range2=B1)*(Range3=C1))
=SUMPRODUCT((RNGA="A")*(RNGB=1)*RNGC)
=SUMPRODUCT((Sheet2!A1:A100>=Sheet1!A1)*(Sheet2!A1:A100<=DATE(YEAR(Sheet1!A1
),MONTH(Sheet1!A1)+Sheet1!A2-1,DAY(Sheet1!A1)))*(Sheet2!B1:B100))
=SUMPRODUCT((TEXT(B1:B5,"mmm")="Oct")*A1:A5)
=SUMPRODUCT((YEAR(B2:B9)=2002)*(MONTH(B2:B9)=11)*(A2:A9="Pending"))
=SUMPRODUCT(A2:A5;B2:B5)
=SUMPRODUCT(A2:A56,B2:B56)+SUMPRODUCT(A58:A62,B58:B62)+SUMPRODUCT(A64:A75,B6
4:B75)
=SUMPRODUCT(ABS(A1:A10)*1)
=SUMPRODUCT(COUNTIF(INDIRECT("Week"
&(ROW(INDIRECT("1:11")))&"!D419"),"CORP"))
=SUMPRODUCT(MOD(COLUMN(I8:IS8),2),I8:IS8)
=SUMPRODUCT(MONTH(B1:B5=9)*A1:A5)
=SUMPRODUCT(N(C2:C765={"B","TB"}))
=SUMPRODUCT(N(EXACT(C2:C765,{"B","TB"})))
=SUMPRODUCT(SUMIF(INDIRECT("'Respondent
NO"&ROW(INDIRECT("1:50"))&"'!C1"),1,INDIRECT("'Respondent
NO"&ROW(INDIRECT("1:50"))&"'!A1")))
=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:35)&"!B1"),TRUE,INDIRECT(ROW(1:35)&"!A5")))
=SUMPRODUCT(x^(ROW(INDIRECT("1:"&CEILING(T,1)))*{1,-1}+T*{0,1}))
=SUMPRODUCT((A1:A6>C1)*B1:B6)

=SUMPRODUCT((MOD(COLUMN(J3:IV3)-3,7)=0)*(J3:IV3))
Every 7th column

=SUMPRODUCT((MOD(ROW(A1:A10),2)=0)*(A1:A10))
To find every 2nd row, starting with row 2 (will add rows 2, 4, 6, 8, and
10):

=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10))
To find every 2nd row, starting with row 1 (will add rows 1, 3, 5, 7, and
9):

=SUMPRODUCT((MOD(ROW(A1:A10),3)=0)*(A1:A10))
To find every 3rd row, starting with row 3 (will add rows 3, 6, and 9): To
find every 4th, 5th, 6th, etc..., starting with row 4, 5, 6, etc..., just
change the number 3 in the above formula to 4, 5, 6, etc.

=SUMPRODUCT((MOD(ROW(A1:A10),3)=1)*(A1:A10))
To find every 3rd row, starting with row 1 (will add rows 1, 4, 7, and 10):
To find every 4th, 5th, 6th, etc..., starting with row 1, just change the
number 3 in the above formula to 4, 5, 6, etc.



SUMIF - Examples of Syntax

=SUMIF($A$2:$A$6,">06/01/02",$B$2:$B$6)
=SUMIF($A$2:$A$6,$C2&$D2,$B$2:$B$6)
=SUMIF($A1:$A6,FALSE,C1:C6)
=SUMIF($A1:$A6,TRUE,C1:C6)
=SUMIF($B$2:$M$2,"<="&mth,$B4:$M4)
=SUMIF($P$5:$P$4630,"81*LC",DB$5B$4630)-SUMIF($P$5:$P$4630,"81*LLC",DB$5
B$4630) WILDCARDS *,?
=SUMIF(---,">"&A75,---)
=SUMIF(A1:A10,1)
=SUMIF(A1:A100,">"&MAX(A1:A100)-7,B1:B100)
=SUMIF(A1:A20,"<0",B1:B20)/COUNTIF(A1:A20,"<0")
=SUMIF(A1:A4,"<>#N/A")/MAX(1,COUNT(A1:A4))
=SUMIF(A1:A6,">"&C1,B1:B6)
=SUMIF(A1:A6,">"&DATEVALUE("6/30/01"),B1:B6)
=SUMIF(A1:C11,">"&E2)
=SUMIF(A1:C11,">"&sheet2!E2)
=SUMIF(A2:A25,">="&E2,B2:B25)-SUMIF(A2:A25,">"&F2,B2:B25)
=SUMIF(A2:A5,">"&D1,B2:B5)-SUMIF(A2:A5,">"&E1,B2:B5)
=SUMIF(A3:A13,">0")+SUMIF(A3:A13,"<0")
=SUMIF(A7:A48."T".E7:E48)
=SUMIF(B2:B13,">"&A75)
=SUMIF(C1:C4,"LC",B1:B4)
=SUMIF(D5:F11,">70",D5:F11)
=SUMIF(Details!$A$1:$A$999,$A2,Details!B$2:B$999)
=SUMIF(E2:E8;">"&TODAY();F2:F8)
=SUMIF(MKTCAP,">=5000000000") - SUMIF(MKTCAP,">20000000000")
=SUMIF(P25,"<>*LLC",DB2B5) WILDCARDS *,?
=SUMIF(range,">=" & "2/1/2001",sum_range) - SUMIF(range,">"
&"2/28/2001",sum_range)
=SUMIF(Range,>1 billion,Range) - SUMIF(Range,>5 Billion,Range)
=SUMIF(TheRange,"<0)
=SUMIF(therange,">0")

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[arthurbr]

Correct me if I'm wrong, but wouldn't it be easier to use a pivot table ?
You will need to use a named range of course.
"Define Name" as
=OFFSET(Sheet2!$A$1;0;0;COUNT(Sheet2!$A:$A);2)

 

[KenWright]

Just to count names in a column?

Assuming the cells without names are blank, then the OP has already been given the most efficient answer by far, which is to simply use COUNTA().

The examples I gave simply address the initial part of the question relating to the use of wildcards within SUMPRODUCT().

Pivot tables are the best invention in the world to me, but a sledgehammer to crack a nut in this instance, and certainly nowhere near as efficient from a resource perspective. (At least not for the problem stated, ie counting names)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Also, if there really are blanks in the data in Col A as stated, then your formula would return an incorrect range as the Pivot Table source, because you are using COUNTA to detrmine the height argument, which only works if you either:-

- have no blanks, or
- cater for any blanks

Neither of which are the case in your defined range name.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[arthurbr]

Ok Ken, you're right as usual.I withdraw my suggestion, and thank you for your remarks

 

[KenWright]

LOL - Don't know about the 'as usual' bit, but you're welcome.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------