Need Help With a Formula

Topic

New Reply

Hi all,

Here’s a screenshot of the Google Sheet I’m working with:

And here’s the formula in B2:

=COUNTIF('2016'!D:D,"Bad timing on their end")+COUNTIF('2017'!D:D,"Bad timing on their end")+COUNTIF('2018'!E:E,"Bad timing on their end")+COUNTIF('2019'!E:E,"Bad timing on their end")+COUNTIF('2020'!G:G,"Bad timing on their end")+COUNTIF('2021'!J:J,"Bad timing on their end")+COUNTIF('2022'!M:M,"Bad timing on their end")+COUNTIF('2023'!L:L,"Bad timing on their end")+COUNTIF('2024'!L:L,"Bad timing on their end")

I’m looking for a way to drag this formula (B2) down the column, such that I don’t have to change “Bad timing on their end” in each row. (Everything else in the formula remains the same.)

Can anyone help?

Thanks so much!

Reply | Quote

Replies

I figured it out!

=COUNTIF('2016'!D:D,$A2)+COUNTIF('2017'!D:D,$A2)+COUNTIF('2018'!E:E,$A2)+COUNTIF('2019'!E:E,$A2)+COUNTIF('2020'!G:G,$A2)+COUNTIF('2021'!J:J,$A2)+COUNTIF('2022'!M:M,$A2)+COUNTIF('2023'!L:L,$A2)+COUNTIF('2024'!L:L,$A2)

Reply | Quote

How about =COUNTIFS('2016'!D:D,$A2,'2017'!D:D,$A2,'2018'!E:E,$A2,'2019'!E:E,$A2,'2020'!G:G,$A2,'2021'!J:J,$A2,'2022'!M:M,$A2,'2023'!L:L,$A2,'2024'!L:L,$A2)

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote

Thanks so much, Paul!

Reply | Quote