IF Function

Copper Contributor

I have two worksheets in the same workbook.  If it finds the model number "BE20" in column C of Sheet 1, then I'd like it to copy specific cells within that row (ie, j to ac on that row), if it does not find it in that column then all of those cells would be 0 (but I know it won't be 0, I will make sure that the model numbers I'm using in my formula are actually in sheet 1).

 

I've been trying to use the IF function for this but I've had no success.  I know there are two instances of BE20 in sheet 1 but it's still not finding them.

 

And I don't even know if the cells within the row that it finds can be copied automatically from one sheet to the other. 

 

And fyi, I've kept the same formatting for all of the copied cells on both sheets.

 

This is what I tried:

 

=IF(Sheet1!C:C=BE20_,Sheet1!J10:AC10=Sheet2!J10:AC10,0)

 

Any help would be greatly appreciated.  Can I even do this? lol

4 Replies

@QwertyBird , you have few records in Sheet1 for the each model. In Sheet2 you'd like to have the summary for the models or just repeat records from Sheet1?

@Sergei BaklanHi Sergei, thank you so much for replying. 

 

I am expecting that sheet 1 will have 3500+ lines by the time everything is said and done and because of that it would be really nice to have a formula on sheet 2 to just have totals for all of the cells that have data for the specific model numbers. 

 

Right now I've just done a straight copy of the cells from sheet 1 to sheet 2 and put it in a table so that it can be sorted by model number and then totalled with a formula periodically.   If I do it this way, then each sheet will have 3500+ lines and I'm worried about it eventually getting corrupted because it's going to be such a large, unwieldy document.  I know - backup, backup, backup lol.

 

I have an old document that had a formula in sheet2 that clearly doesn't work and I don't think it ever did but this is the old sheet2 model totalling formula:

 

=SUMIF('Sheet1'!C1:'Sheet1'!C8960,B8,'Sheet1'!P1:'Sheet1'!P8960)

 

I don't have very much experience with the IF functions but I know I'll understand it once it's shown to me how to think, to get the formula I need lol.

 

Thank you so much for your help.  It is greatly appreciated :)

Hi @QwertyBird ,

 

In general SUMIF is the correct function in your case. What do you mean it doesn't work, it's slow performance or what? If so on which version of Excel you are?

 

I emulated your table in attached with formula

=SUMIF(Sheet1!$C$1:$C$8960,$A8,Sheet1!J$1:J$8960)

in the second sheet, and with dynamic ranges in third one

=SUMIF(Sheet1!$C$8:INDEX(Sheet1!$C$8:$C$100000,COUNTA(Sheet1!$C$8:$C$100000)),$A8,Sheet1!J$8:INDEX(Sheet1!J$8:J$100000,COUNTA(Sheet1!$C$8:$C$100000)))

, drag them down and to the right for other cells.

 

You may check how it works in your environment. If quite slow remove one of these sheets and check again.

 

@Sergei Baklan, Thank you SO much for your help.  It is greatly appreciated :)