countifs gives me #VALUE! trying to count if two criterea are met.

I am trying to get a count of employees in certain areas on a schedule recap. I am working on a modified template of a project planner from excel.
column A has drop down lists of all possible areas
columns B through H have title, description of work, and drop down lists of employees names along with other linked cells to generate graphs

most sense to me is as followed:

=COUNTIFS(A6:A57,Employees!E2,project!B6:H57,Employees!B5)

A6:A57 is the range in which "the area" is located

Employees!E2 refers to another sheet with "the area"

B6:H57 is the range in which an employees name will be located

Employees!B5 refers to another sheet with an employees name

The ranges in countifs must be of similar dimensions.
You first have A6:A5 - a 1 column range
Then you have B6:H57 - a 7 column range.

Try
=SUMPRODUCT((A6:A57=Employees!E2)*(B6:H57=Employees!B5))

still having the same error. after looking up the SUMPRODUCT it stated that the array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

The formula I posted has only 1 argument in the sumproduct.
The 2 calculations are done first with simple True's and False's, and returned as a single array to sumproduct.

If that formula also returned #Value! error, that is likely due to pre-existing #Value! Errors within the ranges A6:H57

Originally Posted by atlasguy

=COUNTIFS(A6:A57,Employees!E2,project!B6:H57,Employees!B5)

I am not entirely sure but I want to say that COUNTIFS criteria ranges all need to be on the same sheet. In your formula they are not.

Originally Posted by Zer0Cool

I am not entirely sure but I want to say that COUNTIFS criteria ranges all need to be on the same sheet. In your formula they are not.

No they don't 'have' to be on the same sheet.
It doesn't make much logical sense for them not to be, but it won't cause the #Value! Error.

I've gone on the assumption that the formula is actually entered on the project sheet.
So range A6:A57 would still refer to that sheet.
And the sheet reference on project!B6:H57 is actually superfluous.
I just removed it from my suggested formula.

I changed how the data was arranged and it fixed my issue thank you very much for helping

You're welcome.

Would you mind elaborating a little bit on how exactly you changed it?
For the benefit of future readers who search the forum with similar problem.

yes,

like you had mentioned since I was searching multiple columns for data it made it have the #value! error.
I rearranged the data on my sheet to be in single columns and turned the text 90 degrees so I could read the info. after that I used the countifs formula to search for my criteria separate in each column

I had to make it rather long but it works

unless someone else has a better solution

this is what I used

=COUNTIFS(A6:A53,"Body Shop",B6:B53,"Gawlowski, Tom")+COUNTIFS(A6:A53,"Body Shop",C6:C53,"Gawlowski, Tom")+COUNTIFS(A6:A53,"Body Shop",D6:D53,"Gawlowski, Tom")+COUNTIFS(A6:A53,"Body Shop",E6:E53,"Gawlowski, Tom")+COUNTIFS(A6:A53,"Body Shop",B6:B53,"Hudson, Don")+COUNTIFS(A6:A53,"Body Shop",C6:C53,"Hudson, Don")+COUNTIFS(A6:A53,"Body Shop",D6:D53,"Hudson, Don")+COUNTIFS(A6:A53,"Body Shop",E6:E53,"Hudson, Don")

You really should use the sumproduct...

=SUMPRODUCT((A6:A53="Body Shop")*(B6:E53="Gawlowski, Tom"))

thank you very much I used daddylonglegs formula and it worked perfectly after I fixed the quotes around the text. you guys are awesome! I'm going to have to learn a little bit more about how sumproduct works but I'm extremely happy now

so one more quick question.
can I adapt this formula to use data off a separate sheet?
since I use drop down menus for data on the main sheet it would be easier to modify the list if my employees change instead of hard punching names in the formula?

right now it wont let me.