Forum Discussion
help with IF Formula
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 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?
My pleasure!
To convert data into a table:- Select a cell in the range
- Home tab, Styles Section, "Format as Table", verify your table range and check if your table has headers.
- 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 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)