Use count and ranges in same formula

[pasjauo]

Hi guys

I have stumbled upon a problem with a work assignment.

I have sales rep from different part of the country (divided in to 3 areas) and they input every time they make a visit and which category the visit is.

My boss would like to know per week how the different areas act upon his prioritizing of categories of the visits.

So i have date of visit, category of visit and employee name. I do not have the area pre divided.

I then tried to define an area with the names of the people in that area and then put it in my formula which looks like this:

=COUNTIFS(DATECOLUMN,<DATE,DATECOLUMN,>DATE,NAMECOLUMN,AREA1,VISITCAT,REFERENCETOCAT)

AREA1 is my range with names. I would like a simple formula instead of doing above formula for each name and combining them in the end.

Any tips?

 

[jasonb75]

It should work if you enclose it in SUM then array confirm it with Ctrl Shift Enter.

=SUM(COUNTIFS(DATECOLUMN,<DATE,DATECOLUMN,>DATE,NAMECOLUMN,AREA1,VISITCAT,REFERENCETOCAT))

 

[pasjauo]

It should work if you enclose it in SUM then array confirm it with Ctrl Shift Enter.

=SUM(COUNTIFS(DATECOLUMN,<DATE,DATECOLUMN,>DATE,NAMECOLUMN,AREA1,VISITCAT,REFERENCETOCAT))

It works perfectly, thank you!