Share via

I need an Excel formula to subtract a specific number in a column that corresponds with a word in a different row? Details Below.

Anonymous
2024-12-16T23:11:26+00:00

I need an Excel formula to subtract a specific number in a column that corresponds with a word in a different column from the total of that column. I actually need these to add up somewhere else.

There is a pricing tab, the word is ALT and it is in column A, I need the "Cost of Labour" Column J added up minus the rows that correspond with the word ALT.

I then need just the rows with the word ALT in them to add up in a separate place.

I have two sheets, pricing and materials.

ALT is in Column A on both spread sheets.

The total pricing cost is in column J or 10

The total material cost is in column I or 9

I need these totals on each individual sheet minus any rows that correspond with the word ALT in column A.

I then also need the total of the rows that have the word ALT in a separate cell.

I am trying to separate the alternates from my base bid cost. I need the math for all alternates done separately and the math for the base bid done separately.

Thank you very much!

Microsoft 365 and Office | Excel | Other | Android

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Accepted answer
  1. Anonymous
    2024-12-20T06:07:36+00:00

    Hi Barbara Zalewski ,

    Is the situation shown in the image below what you want to achieve?

    For the data source, let's assume you have a worksheet named "Pricing Worksheet" where the light green part of column J contains the data that meets the criteria.

    You can use the FILTER function with the following formula to display the results (I noticed you posted in the Android section, and theoretically, this function should also be available in the latest version of the Excel Android app):

    The result is shown in the image below.

    Looking forward to your reply.

    Best Regards,

    Jonathan Z - MSFT | Microsoft Community Support Specialist

    0 comments
Accepted answer
  1. Anonymous
    2024-12-17T04:13:39+00:00

    Hi Barbara Zalewski,

    Thanks for visiting Microsoft Community.

    From your description, it seems that this formula will work.

    =SUMIFS(J:J, A:A, "<>*ALT*")

    This formula will sum all the rows in column J that meet the condition of column A not containing the word "ALT". You can change J:J to I:I and use it in another sheet.

    Does this meet your needs?

    Best Regards,

    Jonathan Z - MSFT | Microsoft Community Support Specialist

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-12-17T21:36:25+00:00

    This worked beautifully on the pricing sheet, thank you very much Jonathan Z!!

    However, on the materials sheet there are some values that are #N/A because those rows don't have a quantity and I used the formula below to add the column up. Is there a way to combine these two formulas??

    To add all of the items in these columns that don't equal ALT or #N/A so I can get a number value for the total of the column.

    =SUM(IF(ISNA(I3:I34),0,I3:I34))

    =SUMIFS(I3:I34, A3:A34, "<>*ALT*")

    Alternately; Is there a way add the rows that DO equal ALT, listed one by one in a column on a separate sheet?

    Lets say three rows say ALT on my pricing worksheet, I want them to populate on the Alternates tab, in individual rows, in the same column, so each row can then be individually marked up here. I do not need the SUM of these.

    (Is there a way for a cell to appear as zero or blank if a row isn't used, rather than #N/A which prevents math?)

    Thank you again!!

    0 comments
  2. Anonymous
    2024-12-18T02:39:22+00:00

    Hi Barbara Zalewski ,

    I'm glad I could help you. You can mark "Yes" under my reply, which will greatly assist me in providing technical support in the community and help other users with similar needs find answers more quickly.

    For your first question, you can use the following formula to combine two conditions:

    =SUMIFS(I3:I34, I3:I34, "<>#N/A", A3:A34, "<>*ALT*")

    For your second question, I'm not quite sure what you specifically want to achieve. If you want to list the rows containing "ALT" in another sheet, you can use the FILTER function, for example:

    =FILTER(A1:I34, ISNUMBER(SEARCH("ALT", A3:A34)))

    This will filter out the rows in the range where column A contains "ALT," but it will lose the relative position of these rows in the original worksheet.

    If you just want to make the rows containing "ALT" more noticeable for marking purposes, you can use conditional formatting in the original sheet to highlight those rows that meet the criteria.

    Feel free to share your thoughts with me.

    Best Regards,

    Jonathan Z - MSFT | Microsoft Community Support Specialist

    0 comments
  3. Anonymous
    2024-12-19T17:05:32+00:00

    Yes, basically I need the row with the word "ALT" filtered to a different sheet, however, I only need the number in Column J from the pricing sheet to appear on the other sheet.

    This formula did not work. =FILTER('Pricing Worksheet'!A1:J34,ISNUMBER(SEARCH("ALT",'Pricing Worksheet'!A3:A34)))

    I just get #VALUE

    There may be several different rows, I need all of the rows to populate on the new sheet, their original position is not important as long as each number is still in its own row!

    The pricing sheet has several columns of information, column A is where the word ALT appears, Column J is where the total cost of labor appears. I need the number in column J to populate on the other sheet, no further info.

    Thank you again!

    0 comments