CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
PapZapDo123
PapZapDo123

Reputation: 1

Google Sheets | Which formula is right for me? COUNTIFS / ARRAY?

Within my team, I want to document on a dashboard how many pages team members have created each month.

Across my Google sheet, I have numerous tabs in which the team can select their name (to mark that they have completed a page) and the month (to show when it was completed).

How can I compile the name and month completed across all months into a formula?

This is a formula I added, but it won't allow me to add any more values after "team member name".

=COUNTIFS({'Month 1'!A:G;'Month 2'!A:G},"Team member name", "Month page completed")

Upvotes: 0

Views: 45

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

Try this:

If month name is in one of columns then you can:

=rows(
query({'Month 1'!A:G;'Month 2'!A:G},"select Col1 where Col2 ='Team member name' and Col3 ='Month page completed'",0)
)

I assume that: Col2 is a column where team members' names are kept Col3 contains months when page was completed

ColX is an index of column within range A:G (A is Col1, B is Col2, etc.)

Rows() counts how many rows is returned by query.

If you want to reference your team member names from the sheet, you can do this with:

=rows(
    query({'Month 1'!A:G;'Month 2'!A:G},"select Col1 where Col2 ='"&A1&"' and Col3 ='"&A2&"'",0)
    )

where A1 and A2 are cells with team member name and month page completed

Upvotes: 1

Related Questions