i have some data that is organized terribly, and it's HUGE.

i have sales numbers monthly by sales rep

the month column has the number of sales for that rep for that month per store


however a rep covers more than one store, so there are multiple entries for
each month for each rep.

i want to count how many entries there are in a given month for a given rep.

i can do the sum easy enough =SUMIF(H:H,"Max",U:U)
it checks H for "Max" and adds up how many entries there are in U for each
time Max appears in H


but i dont want a total, i just want the count :(

sorry for the long description. this has got my head in a spin

If your table just has one month of data, then you can replace sumif with
countif, dropping the final argument: =countif(h:h,"Max)

"Balhar" wrote:

i have some data that is organized terribly, and it's HUGE.

i have sales numbers monthly by sales rep

the month column has the number of sales for that rep for that month per store


however a rep covers more than one store, so there are multiple entries for
each month for each rep.

i want to count how many entries there are in a given month for a given rep.

i can do the sum easy enough =SUMIF(H:H,"Max",U:U)
it checks H for "Max" and adds up how many entries there are in U for each
time Max appears in H


but i dont want a total, i just want the count :(

sorry for the long description. this has got my head in a spin

there are many months so the countif wont work
so there is a Max for every time max made a sale, regardless of what month
it was in

i would have to do a =countif(H:H,"Max"U:U)

but you cannot do count if counting one column and using another as the
criteria



"bpeltzer" wrote:

If your table just has one month of data, then you can replace sumif with
countif, dropping the final argument: =countif(h:h,"Max)

"Balhar" wrote:

i have some data that is organized terribly, and it's HUGE.

i have sales numbers monthly by sales rep

the month column has the number of sales for that rep for that month per store


however a rep covers more than one store, so there are multiple entries for
each month for each rep.

i want to count how many entries there are in a given month for a given rep.

i can do the sum easy enough =SUMIF(H:H,"Max",U:U)
it checks H for "Max" and adds up how many entries there are in U for each
time Max appears in H


but i dont want a total, i just want the count :(

sorry for the long description. this has got my head in a spin

You could use COUNT and IF together as an array formula.

So say in H is where Max is and C is the month you want to look for and
count if U has a value in it.

=COUNT(IF(C1:C200="January",IF(H1:H200="Max",IF(U1 :U2000,U1:U200,""))))

Commit with Ctrl-Shift-Enter instead of just enter. This will create
the curly brackets around the formula like.

{=COUNT(IF(C1:C200="January",IF(H1:H200="Max",IF(U 1:U2000,U1:U200,""))))}

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=502645

ok this works

=COUNT(IF(H1:H2000="Max",IF(U1:U20000,U1:U2000,"" )))

is therre a way i can get it to look at all of column H and U?

everytime i take out the numbers behind the letters i get an error




"SteveG" wrote:


You could use COUNT and IF together as an array formula.

So say in H is where Max is and C is the month you want to look for and
count if U has a value in it.

=COUNT(IF(C1:C200="January",IF(H1:H200="Max",IF(U1 :U2000,U1:U200,""))))

Commit with Ctrl-Shift-Enter instead of just enter. This will create
the curly brackets around the formula like.

{=COUNT(IF(C1:C200="January",IF(H1:H200="Max",IF(U 1:U2000,U1:U200,""))))}

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=502645

Unfortunately, you would have to enter it like this.

=COUNT(IF(H1:H65535="Max",IF(U1:U655350,U1:U65535 ,"")))

Because this is an array formula, commit with Ctrl-Shift-Enter. This
looks at all rows in the sheet but the last one. That is because Excel
automatically changes H1:H65536 to H:H which the formula does not work
on.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=502645

Try COUNTIF:
http://www.officearticles.com/excel/...ft_excel.h tm

Or, perhaps you can use Data--Subtotals?
http://www.officearticles.com/excel/...soft_excel.htm
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Balhar" wrote in message
...
i have some data that is organized terribly, and it's HUGE.

i have sales numbers monthly by sales rep

the month column has the number of sales for that rep for that month per
store


however a rep covers more than one store, so there are multiple entries
for
each month for each rep.

i want to count how many entries there are in a given month for a given
rep.

i can do the sum easy enough =SUMIF(H:H,"Max",U:U)
it checks H for "Max" and adds up how many entries there are in U for each
time Max appears in H


but i dont want a total, i just want the count :(

sorry for the long description. this has got my head in a spin