software

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 storehowever 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,quot;Maxquot;,U:U)
it checks H for quot;Maxquot; and adds up how many entries there are in U for each
time Max appears in Hbut 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,quot;Max)

quot;Balharquot; wrote:

gt; i have some data that is organized terribly, and it's HUGE.
gt;
gt; i have sales numbers monthly by sales rep
gt;
gt; the month column has the number of sales for that rep for that month per store
gt;
gt;
gt; however a rep covers more than one store, so there are multiple entries for
gt; each month for each rep.
gt;
gt; i want to count how many entries there are in a given month for a given rep.
gt;
gt; i can do the sum easy enough =SUMIF(H:H,quot;Maxquot;,U:U)
gt; it checks H for quot;Maxquot; and adds up how many entries there are in U for each
gt; time Max appears in H
gt;
gt;
gt; but i dont want a total, i just want the count
gt;
gt; sorry for the long description. this has got my head in a spin

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

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

quot;Balharquot; gt; wrote in message
...
gt;i have some data that is organized terribly, and it's HUGE.
gt;
gt; i have sales numbers monthly by sales rep
gt;
gt; the month column has the number of sales for that rep for that month per
gt; store
gt;
gt;
gt; however a rep covers more than one store, so there are multiple entries
gt; for
gt; each month for each rep.
gt;
gt; i want to count how many entries there are in a given month for a given
gt; rep.
gt;
gt; i can do the sum easy enough =SUMIF(H:H,quot;Maxquot;,U:U)
gt; it checks H for quot;Maxquot; and adds up how many entries there are in U for each
gt; time Max appears in H
gt;
gt;
gt; but i dont want a total, i just want the count
gt;
gt; 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,quot;Maxquot;U:U)

but you cannot do count if counting one column and using another as the
criteria
quot;bpeltzerquot; wrote:

gt; If your table just has one month of data, then you can replace sumif with
gt; countif, dropping the final argument: =countif(h:h,quot;Max)
gt;
gt; quot;Balharquot; wrote:
gt;
gt; gt; i have some data that is organized terribly, and it's HUGE.
gt; gt;
gt; gt; i have sales numbers monthly by sales rep
gt; gt;
gt; gt; the month column has the number of sales for that rep for that month per store
gt; gt;
gt; gt;
gt; gt; however a rep covers more than one store, so there are multiple entries for
gt; gt; each month for each rep.
gt; gt;
gt; gt; i want to count how many entries there are in a given month for a given rep.
gt; gt;
gt; gt; i can do the sum easy enough =SUMIF(H:H,quot;Maxquot;,U:U)
gt; gt; it checks H for quot;Maxquot; and adds up how many entries there are in U for each
gt; gt; time Max appears in H
gt; gt;
gt; gt;
gt; gt; but i dont want a total, i just want the count
gt; gt;
gt; gt; 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=quot;Januaryquot;,IF(H1:H200=quot;Maxquot;,IF(U1 :U200gt;0,U1:U200,quot;quot;))))

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

{=COUNT(IF(C1:C200=quot;Januaryquot;,IF(H1:H200=quot;Maxquot;,IF(U 1:U200gt;0,U1:U200,quot;quot;))))}

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502645ok this works

=COUNT(IF(H1:H2000=quot;Maxquot;,IF(U1:U2000gt;0,U1:U2000,quot;quot; )))

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

quot;SteveGquot; wrote:

gt;
gt; You could use COUNT and IF together as an array formula.
gt;
gt; So say in H is where Max is and C is the month you want to look for and
gt; count if U has a value in it.
gt;
gt; =COUNT(IF(C1:C200=quot;Januaryquot;,IF(H1:H200=quot;Maxquot;,IF(U1 :U200gt;0,U1:U200,quot;quot;))))
gt;
gt; Commit with Ctrl-Shift-Enter instead of just enter. This will create
gt; the curly brackets around the formula like.
gt;
gt; {=COUNT(IF(C1:C200=quot;Januaryquot;,IF(H1:H200=quot;Maxquot;,IF(U 1:U200gt;0,U1:U200,quot;quot;))))}
gt;
gt; HTH
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=502645
gt;
gt;


Unfortunately, you would have to enter it like this.

=COUNT(IF(H1:H65535=quot;Maxquot;,IF(U1:U65535gt;0,U1:U65535 ,quot;quot;)))

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: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502645i just set it to H1:H8000, it'll be enough for a while

i cannot thank you enough, you have saved me a LOT of time, and made me look
good for the boss

quot;SteveGquot; wrote:

gt;
gt; Unfortunately, you would have to enter it like this.
gt;
gt; =COUNT(IF(H1:H65535=quot;Maxquot;,IF(U1:U65535gt;0,U1:U65535 ,quot;quot;)))
gt;
gt; Because this is an array formula, commit with Ctrl-Shift-Enter. This
gt; looks at all rows in the sheet but the last one. That is because Excel
gt; automatically changes H1:H65536 to H:H which the formula does not work
gt; on.
gt;
gt; HTH
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=502645
gt;
gt;


No problem. There are plenty of times that people have helped me out
too.Cheers,
Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502645