Recurring find a replace

[GOGOBE22]

Hi,
I have Excel 2016.
In each row, in cell B I have a name and then in cells C to I i have many different countif functions searching for that name in cell B on another spreadsheet. I have 50 rows with 50 different names and corresponding countif functions.
If i need to make a change to the countif functions range I need to copy the new sets of functions to all the rows and then one by one use "find and replace" to find the countif criteria (the name) and replace it with the correct name in cell B of the corresponding row.
I would like to automate this process, couldn't find a solution with chat GPT.
Any ideas please?

[TMS]

Are you still using 2010?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[Croweater]

Change your name in quotes to a cell (change "Dan" to B2) and drag down.

[avk]

May be
In "C2" =COUNTIF(Sheet2!$B$2:$B$15,$B2)

[jindon]

Is this what you are after?
C2;
=SUMPRODUCT((Sheet2!$A$1:$A$15<>"Friday")*(Sheet2!$A$1:$A$15<>"Saturday")*(Sheet2!$B$1:$B$15=B2))
D2;
=SUMPRODUCT(((Sheet2!$A$1:$A$15="Friday")+(Sheet2!$A$1:$A$15="Saturday"))*(Sheet2!$B$1:$B$15=B2))

Then copy both down.

[Sintek]

This is the 4th post with exact same requirement?

[jindon]

C2;
=SUMPRODUCT((Sheet2!$A$1:$A$15<>"Friday")*(Sheet2!$A$1:$A$15<>"Saturday")*(Sheet2!$B$1:$B$15=B2))
D2;
=SUMPRODUCT(((Sheet2!$A$1:$A$15="Friday")+(Sheet2!$A$1:$A$15="Saturday"))*(Sheet2!$B$1:$B$15=B2))

Then copy both down.

[GOGOBE22]

Change your name in quotes to a cell (change "Dan" to B2) and drag down.

So simple, works! Thanks

[Glenn Kennedy]

Gogobear. Please take a moment and actually READ the rules that you agreed to stick to.



https://www.excelforum.com/forum-rul...rum-rules.html

Please note Rule 5 in particular. You have now got 4 identical threads. You can expect to be banned from this forum if you continue to post multiple threads on EXACTLY the same subject.

I have merged the 3 threads that remain open.