Best way to make a long if statement?

**mike12345** · 12-16-2011, 02:31 PM

I have about 30 seperate unique codes for various different revenue accounts. The codes are 4 cells to the left of each total revenue number (which is the number i want to pull). I also have a report that has the the revenue account name and what should be the total revenue number in the cell next to it. Let's say the code for "Revenue Account A" on the Report worksheet is T29D and the code for "Revenue Account B" is G56W. If the codes were in column J and the revenue amounts were in column F, I would want the formula where the total revenue number should appear to say If(J14="T29D",F14,If(J14="G56W",F14,If(...... And so on down the line for those 30 codes. What is the best way to do this. It does not necessarily have to be and if statement, I just thought this would be the only way to achieve the desired results. Any input is appreciated.

**daddylonglegs** · 12-16-2011, 02:41 PM

Are you pulling amounts for different codes into one cell or just trying to sum separately for each individual code?

**mike12345** · 12-16-2011, 02:47 PM

I am going to drag the formula all the way to the bottom of the spreadsheet so each cell with the formula should have a different revenue amount depending on whether or not it found the appropriate code in column J (just one number in each cell, not multiple numbers in one cell)

**daddylonglegs** · 12-16-2011, 02:53 PM

OK, I think I understand. If you have the codes in a list then name that list "CodeList" and then use this formula in row 14 copied down

=IF(COUNTIF(Codelist,J14),F14,"")

Then you can sum that column for the total

Alternatively you could probably get the total in one go with this formula

=SUMPRODUCT(SUMIF(J:J,Codelist,F:F))

If you don't want to name the list of codes you could just use the cell references of the list in either of those formulas