COUNTIF problem

  • Thread starter Thread starter SUPAFLY
  • Start date Start date
S

SUPAFLY

I want to do a count with more than one criteria in two different
columns. see below, please help :confused:

count "name" in column A ONLY if the following characteristics (small,
big or medium) apply conrrespondingly in column B.

It could also be the inverse, count the number of times small, big &
medium appear in column B ONLY if column A = "name"

Please help.
 
=SUMPRODUCT((A1:A1000="name")*(B1:B1000={"small","medium","big"}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
try
=SUMPRODUCT((A1:A100="name")*((B1:B100="small")+(B1:B100="medium") +
(B1:B100="big")))
or
=SUMPRODUCT((A1:A100="name")*(B1:B100={"small","medium","big"}))
 
can I use "*" as a wildcard when naming the criteria (i.e. small*
medium*, large*, etc.) for the suggested formulas
 
You could perhaps try this:

Data list - A1:B20
Name to count - C1
Size & Item to count - C2

Enter the name you're looking for into C1, and enter the size and the item
into C2, and enter this formula into C3:

=SUMPRODUCT((A1:A20=C1)*(B1:B20=C2))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


can I use "*" as a wildcard when naming the criteria (i.e. small*,
medium*, large*, etc.) for the suggested formulas?
 
Not exactly, but this should do what you want

=SUMPRODUCT((A1:A1000="name")*(LEFT(B1:B1000,{5,6,3})={"small","medium","big
"}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Do you mean the {5,6,3}? That is the length of the strings small, medium and
big, so as to give you your wildcard type requirement.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top