help with IF Formula

Copper Contributor

I am very new to excel so please bare with me :)

 

I'm trying to create an IF formula to do the following:

 

if "x" appears in "selection" fill in the date from column "a" "b" "c" into cell "1" "2" "3"

 

Basically, I need a certain number found in a big selection. once it is found, I need the total data from certain columns to fill in corresponding cells above the original selection

 

idea's on how to write the formula?

20 Replies

Hello @lorie3345,

 

Unfortunately, your description is not completely clear to me. In general, your formula would look something like this:

=IF(A1="X", "Date from cell", "Not X")

 

@lorie3345 

Better if you provide small sample file to illustrate the question, perhaps it could be done without IF().

@Sergei Baklan I attached a sample

what needs to happen is:

first, the formula needs to find one of the specific card numbers (lets go with 123 for now) from the below selection. 

then, D4 needs to display the total value located in column Z (formula needs to add all GST values for each row which has card number 123)

E4 displays the total column AA, F4 displays the total from column AB and finally, G4 displays the total litres from column Q

 

Basically, I need it to search out specific cards number, add all rows which contain that card number and fill in the specific cells with the specific information I need for my report. This report will be calculated on a monthly basis

I will need to repeat the formula for each card number but the rest will be staying the same.

The information will then be auto-populating a master summary worksheet to get year to date totals based on the sum of the other monthly worksheets

 

Does this make sense or is this impossible to do?

 

@PReagan 

I attached a sample

what needs to happen is:

first, the formula needs to find one of the specific card numbers (lets go with 123 for now) from the below selection. 

then, D4 needs to display the total value located in column Z (formula needs to add all GST values for each row which has card number 123)

E4 displays the total column AA, F4 displays the total from column AB and finally, G4 displays the total litres from column Q

 

Basically, I need it to search out specific cards number, add all rows which contain that card number and fill in the specific cells with the specific information I need for my report. This report will be calculated on a monthly basis

I will need to repeat the formula for each card number but the rest will be staying the same.

The information will then be auto-populating a master summary worksheet to get year to date totals based on the sum of the other monthly worksheets

 

Does this make sense or is this impossible to do?

 

@lorie3345

 

Please review the formulas in the range D4:G6 of the "Jan" tab in the attached workbook.

@PReagan that is perfect, thank you so much!

Could I ask also how you converted the raw data to a table to be used in the formula?

@lorie3345 

 

My pleasure!

To convert data into a table:

  1. Select a cell in the range
  2. Home tab, Styles Section, "Format as Table", verify your table range and check if your table has headers.
  3. In any formula, you may refer to the table using Table1[Header] where "Table1" is the name of your table and "Header" is the name of your header.

@PReagan thank you so much, you have helped me tremendously!

@lorie3345 

 

You're welcome. Happy to help!

@PReagan could I ask you one more thing? I entered in the formula in on my live document but for some reason more lines are adding than the lines associated with the specific card number. I have verified it several times and the card number match so I'm a bit confused on why it is not working

any idea's ?

Ex: card number 123 is listed on rows 7-10 but the formula is adding rows 7-25 instead of stopping at row 10 (row 11 a new card number starts)

 

@lorie3345 

Unfortunately, I am not sure where your error is occurring. It sounds like you have changed the format of the worksheet. In the sheet that you initially shared, the data was contained in rows 12 through 25, there was no data in rows 7 through 10. Perhaps this is where your issue is occurring?

@PReagan I think it may be because my original data "dump" came from a CSV file so I think the card numbers (which are listed as ="123" in the cell) are confusing the formula

I'm just trying to figure out how to convert the CSV data to number text without repasting all the information from the original CSV data file again.... do you think that may be the issue as well?

@lorie3345 

 

I'm not quite sure what you mean by converting to "number text". If the data is coming in as exactly "123" (with quotes) and not exactly 123, then you may use Ctrl+H to find and replace every instance of a quote (") with nothing. The value under Card number column should match exactly the value under the CARD column.

@PReagan with the CSV file the data comes in with an equals sign in front of the card number and the card number has quotes around it (I'm guessing it comes right from some accounting software) if I try to format the cells to anything (text, general whatever) the data in cell turns into a weird combination of numbers and special characters. 

when I try to convert my card numbers to match exactly what is written in the table (card column) I get an error likely due to the data being generated from a different software program (but I'm not sure lol)

So, I was thinking a could try and change the format of the CSV data, then the card numbers would match the card numbers in the table

I can attach my real spreadsheet file (not the sample from before) for you to see what I'm trying to do if that's easier? 

 

@lorie3345 

 

Yes, attaching your spreadsheet would be helpful (Please remove any sensitive data).

@PReagan here it is

You'll see on the Jan 2020 worksheet the first to card numbers are calculating data from rows 76-119 but I believe it's because of the format of the card numbers in the table

The rest of the card numbers listed on the top --> I have manually calculated the totals in the GST, NET

Total etc... categories (I have been using those totals to verify what totals the formula was calculating)

I am in the processing of adding the full card numbers but, again, the formatting seems to be an issue :(

 

@lorie3345 

 

Please review the attached workbook.

@PReagan you are amazing! thank you so much, this is perfect

@lorie3345 

 

My pleasure!