Linking sheets - data consolidation on another sheet

Abiola

Hi Everyone, 

I would like to count the total number of 'In progress' in status column for 'Niger' in Region column on a separate sheet. 

I tried using the formula below but it is giving me #UNPARSEABLE on the new sheet (destination sheet) 

=({ICAP NG State-TECHNICAL Work Plan - FY 21 Range 1}), =IF(Region@row = ("Niger"), COUNT(Status@row = ("In progress")))

The the first bracket represent the name of the source sheet

Would appreciate help with this. 

Thanks! 

Abiola

Matthew Flebbe

Hello Abiola,

You can use the COUNTIFS formula for this task. COUNTIFS allows you to count lines using more than one criteria. In your case, you only want to count the lines that have "Niger" in the Region column AND "In Progress" in the Status column.

A good habit to get into is to rename your cross sheet references so they are easy to remember when you look back at them later. In the example below, I am using REGION and STATUS as the two cross sheet reference names.

The first image below represents your second sheet where you might be creating a metrics table:

Note that the formula I am using points to cell references on line one instead of typing "In Progress", "Waiting", or "Not Started". This is a choice I make normally to allow for easy dragging and copying of the formula to other cells.

The above formula could just as easily be =COUNTIFS({STATUS},"In Progress",{REGION},"Niger") if you would like to manually type the formula for each cell.

The next sheet represents the ICAP NG State-TECHNICAL Work Plan - FY 21 sheet in the example you have provided:

I hope that gets you pointed in the right direction. Please let me know if you have any follow up questions or need clarification on anything above.

best,

Matt

Matthew Flebbe

Hello @Abiola

The dollar sign is used to anchor the formula so when it is dragged or copied, the first line and first column do not change relative to the positioning. Since I was referencing the first line for status, I want the column values to change based on the values in the first line of each column. However, I don’t want the referenced cells to be from the second line when I drag or copy the formula down.

The same is true for the Region values in column one. The $ keeps that column anchored in the formula when dragging or copying to the right.

When using the $, one may use the symbol for a column reference, a line reference, or both.

For example, if you wanted to reference only Niger on every line and on every column, you could use $Region$2 in your formula since Niger is in the second row of the Region column.

Does that make sense?

Matthew Flebbe

Hello Abiola,

You can use the COUNTIFS formula for this task. COUNTIFS allows you to count lines using more than one criteria. In your case, you only want to count the lines that have "Niger" in the Region column AND "In Progress" in the Status column.

A good habit to get into is to rename your cross sheet references so they are easy to remember when you look back at them later. In the example below, I am using REGION and STATUS as the two cross sheet reference names.

The first image below represents your second sheet where you might be creating a metrics table:

Note that the formula I am using points to cell references on line one instead of typing "In Progress", "Waiting", or "Not Started". This is a choice I make normally to allow for easy dragging and copying of the formula to other cells.

The above formula could just as easily be =COUNTIFS({STATUS},"In Progress",{REGION},"Niger") if you would like to manually type the formula for each cell.

The next sheet represents the ICAP NG State-TECHNICAL Work Plan - FY 21 sheet in the example you have provided:

I hope that gets you pointed in the right direction. Please let me know if you have any follow up questions or need clarification on anything above.

best,

Matt

Abiola

Dear @Matthew Flebbe,

Thank you very much for this detailed explanation. Very resourceful and helpful!!

May I ask the reason for the dollar sign in the formula? I think it worked without the symbol.

Many thanks.

Abiola

Matthew Flebbe

Hello @Abiola

The dollar sign is used to anchor the formula so when it is dragged or copied, the first line and first column do not change relative to the positioning. Since I was referencing the first line for status, I want the column values to change based on the values in the first line of each column. However, I don’t want the referenced cells to be from the second line when I drag or copy the formula down.

The same is true for the Region values in column one. The $ keeps that column anchored in the formula when dragging or copying to the right.

When using the $, one may use the symbol for a column reference, a line reference, or both.

For example, if you wanted to reference only Niger on every line and on every column, you could use $Region$2 in your formula since Niger is in the second row of the Region column.

Does that make sense?

Abiola

Super thanks Matthew,

This is very clear, highly illustrative.

Many thanks.

Abiola