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.

 

[Vorlon42]

Mike,
That works great. I have one last question on this thread (knock on wood).
I am assembling my tally sheet, and I need to have it tally up 50 tabs. Instead of manually changing the tab reference in yellow, can I nest a formula to use the cell value from H1 so I can then do a drag-fill?

Thanks!

 

[Michael M]

I don't think so...but I would need to see the worksheet with what you are actually trying to achieve.
Are you saying you have a 50 sheets with tab names "2", "3"....etc ??

 

[Vorlon42]

Dropbox link: Work In Progress-OH Staking Sheet updates+Unit Tab-REV M.xlsx
Yes, I have 50 sheets numbered 1-50. The existing formulas were manually edited to look at the proper sheet.
When you look through it, the formulas will show some range shifts. This is because I had to sort by the units in column A to allow a vlookup on another table to function properly.

 

[Michael M]

I still don't follow.....but you can replace the current formula in B4 of the count sheet with

=SUM(IF(MOD(COLUMN($E$4:$EV$4),3)>0,$E$4:$EV$4,0))

It's an array formula so will need to be entered with CTRL + SHIFT + Enter.....not just Enter, then drag down as far as required

 

[Vorlon42]

Hello,

A little while back you helped me out with the countifs/sumifs issue on this thread.

I am trying to use these formulas in another sheet and I am having no luck at all getting them to work. Here is a dropbox link to the file in question: WIP OH Staking Sheet Lite.xlsx

The formulas are on the '1' tab in column BT.

Thanks!

Steve