Tallying items with multiple criteria - countifs not working

[Vorlon42]

Hello, I have a two-part question.
I am working on a construction unit tally sheet and I am having difficulties getting useful results. I had thought that the Countifs function would serve nicely, however it is not giving useful results.

I am trying to create a tally list as shown on the far right.
The first criteria is 'Action' (The I, R or H in the left column)
The second criteria is the unit. In the case of this screenshot, the units are Pole Height-Class and Ground
The third criteria (which only applies to the guying unit E-13) is quantity. All of the other units are singular.

I though the Countifs function as shown in the formula bar would have worked, however it is giving a #Value error.

I've been beating my head on the wall for a while and I'm getting tired of the squishy sound...

Thanks in advance.

 

[Michael M]

Can you use the XL2BB download ( see my tag) to post a useful sample of your data back, so that we don't have to retype you data
OR
Upload the worksheet to a hosting site, Dropbox for example, then post the link to that upload back here.
But I think SUMPRODUCT will be you prefered formula
AND
using merged cells will not help with any formula you attempt to use.

 

[Vorlon42]

Michael,
I cannot install XL2BB on my work computer and I could not get it to function on my personal laptop, so here is a Box link to a sanitized version of the spreadsheet:

Box | Login

app.box.com

Please let me know if this works.
Unfortunately, I cannot avoid merged cells in this case.
Thanks

 

[Michael M]

Can't access that login....do you have DropBox ??

 

[Vorlon42]

Just set up an account with DropBox. Hopefully this will work...

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Michael M]

Try referring to you leftmost range in each of the set of merged cells

=COUNTIFS(J16:J37,CG16,L16:L37,CF19)

So, col "J" not "J" AND "K"....same with the other ranges

 

[Vorlon42]

Perfect. You were absolutely right about merged cells being a pain in the rear.
One last related question - In column AN - the "Unit E" column, there is a discrete quantity stated on the sheet just to the left. Is there a method to get that quantity factored into the formula? Perhaps multiply the countifs result by the value in the quantity cell?

 

[Michael M]

Do you mean the value in Coll "AM" ?

 

[Vorlon42]

Yes

 

[Michael M]

Change the formula to

=SUMIFS(AM16:AM37,J16:J37,CH16,L16:L37,CF19)