count col-A if col-B = condition

[Balhar]

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

 

[bpeltzer]

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

 

[Anne Troy]

Try COUNTIF:
http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm

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

 

[Balhar]

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

 

[SteveG]

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:U200>0,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(U1:U200>0,U1:U200,""))))}

HTH

Steve

 

[Balhar]

ok this works

=COUNT(IF(H1:H2000="Max",IF(U1:U2000>0,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]

Unfortunately, you would have to enter it like this.

=COUNT(IF(H1:H65535="Max",IF(U1:U65535>0,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

 

[Balhar]

i 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

 

[SteveG]

No problem. There are plenty of times that people have helped me out
too.


Cheers,
Steve