Expand Formula Across Multiple Cells

Hi,

I have added new columns to my spreadsheet and have gone from 60 columns (H11:BO11) to 500 (H11:SM11) but my formula has not automatically continued as it involves multiple cells. The only way I can expand it is to type in each new cell to the formula. Does anyone know an easier way?


This is the current formula from H11 to BO11:

=IF(D11="",0(IF(H11=$H$10,1,0)+IF(I11=$I$10,1,0)+IF(J11=$J$10,1,0)+IF(K11=$K$10,1,0)+IF(L11=$L$10,1,0)+IF(M11=$M$10,1,0)+IF(N11=$N$10,1,0)+IF(O11=$O$10,1,0)+IF(P11=$P$10,1,0)+IF(Q11=$Q$10,1,0)+IF(R11=$R$10,1,0)+IF(S11=$S$10,1,0)+IF(T11=$T$10,1,0)+IF(U11=$U$10,1,0)+IF(V11=$V$10,1,0)+IF(W11=$W$10,1,0)+IF(X11=$X$10,1,0)+IF(Y11=$Y$10,1,0)+IF(Z11=$Z$10,1,0)+IF(AA11=$AA$10,1,0)+IF(AB11=$AB$10,1,0)+IF(AC11=$AC$10,1,0)+IF(AD11=$AD$10,1,0)+IF(AE11=$AE$10,1,0)+IF(AF11=$AF$10,1,0)+IF(AG11=$AG$10,1,0)+IF(AH11=$AH$10,1,0)+IF(AI11=$AI$10,1,0)+IF(AJ11=$AJ$10,1,0)+IF(AK11=$AK$10,1,0)+IF(AL11=$AL$10,1,0)+IF(AM11=$AM$10,1,0)+IF(AN11=$AN$10,1,0)+IF(AO11=$AO$10,1,0)+IF(AP11=$AP$10,1,0)+IF(AQ11=$AQ$10,1,0)+IF(AR11=$AR$10,1,0)+IF(AS11=$AS$10,1,0)+IF(AT11=$AT$10,1,0)+IF(AU11=$AU$10,1,0)+IF(AV11=$AV$10,1,0)+IF(AW11=$AW$10,1,0)+IF(AX11=$AX$10,1,0)+IF(AY11=$AY$10,1,0)+IF(AZ11=$AZ$10,1,0)+IF(BA11=$BA$10,1,0)+IF(BB11=$BB$10,1,0)+IF(BC11=$BC$10,1,0)+IF(BD11=$BD$10,1,0)+IF(BE11=$BE$10,1,0)+IF(BF11=$BF$10,1,0)+IF(BG11=$BG$10,1,0)+IF(BH11=$BH$10,1,0)+IF(BI11=$BI$10,1,0)+IF(BJ11=$BJ$10,1,0)+IF(BK11=$BK$10,1,0)+IF(BL11=$BL$10,1,0)+IF(BM11=$BM$10,1,0)+IF(BN11=$BN$10,1,0)+IF(BO11=$BO$10,1,0)))

excel.PNG

TIA!!!

Try

Formula:

Please Login or Register  to view this content.

Does that work for you?

Note: Solution also posted at cross-post

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added the link for you since it is your first post.

I'm guessing that should be:

Formula:

Please Login or Register  to view this content.

Originally Posted by Taylah

Hi,

Sorry this is the first time I have posted.
Thanks for that!

You miss the point. Did you look at the rule to which you were referred above?

It may be the first time you've posted here, but you have asked the question on at least one other forum. That's not a problem in itself, but you are required to declare it.

Please read this to understand why most forums have this rule: https://www.excelguru.ca/content.php?184

I have attached a screenshot, are you unable to see this from your end?

Yes, but you have been asked for a workbook, NOT a picture of one.

Originally Posted by Taylah

I have attached a screenshot, are you unable to see this from your end?

Unlike some other forums, we support the ability to attach an actual file to your post.

An image attachment has limited value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. Having your file allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution.

The paper clip icon does not work for attachments. To attach a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. On the next screen scroll down and under the Attachments section click the Manage Attachments link, which will show a pop-up window.
3. Click the Choose File button to select a file to attached.
4. Click the Upload button to upload the file.
5. Then click the Close This Window button. Your file is now attached to your post.

I'm not sure what you mean by "it did not expand the whole formula." The given formula (thanks for the typo correction TMS), gives the total number of times where the value in 10 = the value in 11 from columns H to SM.

If you want to drag that formula down so that it gives the value in 11 = value in 12, then value in 12 = value in 13 and so forth, then

Formula:

Please Login or Register  to view this content.

try
=sum(h11:sm11)

or
=SUMPRODUCT(--($H$10:$SM$10=H11:SM11))