Forum Discussion

Stuartfish1's avatar
Stuartfish1
Copper Contributor
Sep 17, 2020

Counting Values in a Column but Using Other Cells to State What's Being Looked Up

I have a table where column A1:A2 states "High", "Medium" "Low". Across the top states "WIP", "New",  "Monitoring" or "Closed", but I want to count the times these appear in another tab based on these criteria. Any assistance greatly appreciated. I've attached the file below.

excel

3 Replies

    • Stuartfish1's avatar
      Stuartfish1
      Copper Contributor
      Sep 17, 2020

      Bennadeau Thanks for this Ben it still doesn't seem to add them up correctly. If I change the mix in  the Details sheet the sums don't appear to be correct. Are the cells also set up so that the fomulae can be dragged to the other cells in the Table? Many thanks.

      • Bennadeau's avatar
        Bennadeau
        Iron Contributor
        Sep 17, 2020

        Stuartfish1 

        I just needed more coffee...

        Attached is a revised version.

        As for the formula dragging, the answer is yes and no.

        Yes you can drag the formula in "B2" to the right.

        But

        No you can't drag the formula in "B2" to the bottom. You will need to change it a bit... I indicated in the formula below where you will need to change your row number if you grad it down.

         

        =COUNTIFS(Detail!$B:$B,Summary!$A$2<--**change this number**,Detail!$A:$A,Summary!B1<--**change this number**)

         

         

Resources