CODEWITHSUNDEEP
excelvba
ygor jesus
ygor jesus

Reputation: 27

Sum all value of row when find a specific word in a column

I want to sum all values of one row (there are a lot of column) if there is a specific value in the first column.

Below follow the problem:

Link with the image

I want to find API Rheology and then find Cond. Time, with verified this two conditions, sum all values of the row.

    enter image description here

Upvotes: 0

Views: 1946

Answers (2)

user4039065
user4039065

Reputation:

The sample data image seemed incomplete so I added a few things.

    Staggered conditions in SUMIFS

The formula in AE65 is,

  Dynamic conditional range

This formula looks down column A for the name of the test (e.g. API Rheology in AC65) then locates the next occurrence of the reported result section (e.g. Cond Time (min) from AD65). Having located that row, it sums columns C:Z.

Upvotes: 0

Magnus
Magnus

Reputation: 11

You need to check the value of the first column with an "IF" function, and then if it's true, use "SUM" to find the total.

For example, if your first column is in a1, and your data goes from a1 to z1, than your formula should look like this:

=IF(A1="[check value]",SUM(A1:Z1),"")

Where you replace [check value] with what value you are looking for and "" with what you want to display if the value is not there. Putting "" will leave the cell blank.

If you want to be more specific and check if the first column contains a value, then you need to use:

=IF(ISNUMBER(SEARCH("[check value]",A1)),SUM(A1:Z1),"")

With the same arguments as before.

If you want to find multiple values than you need to use the AND function, so your function becomes:

=IF(AND(ISNUMBER(SEARCH("[check value]",A1)),ISNUMBER(SEARCH("[check value 2]",A1))),SUM(A1:Z1),"")

This will check if cell A1 contains both strings. If you want to check two different cells, then just replace the second A1 with whatever other cell you want.

If you want to find the string within a column of cells, replace the "A1"s with the list of cells you are looking for the string in. e.g. A1:Z1

The reason this works is because SEARCH returns a number based on where the [check value] is found within cell A1, or an error if the string doesn't appear. ISNUMBER returns TRUE if SEARCH return a number, not an error. Finally, IF checks if ISNUMBER is true or false, and returns the sum of your numbers if true and a blank space if not.

Hope this helped.

Upvotes: 1

Related Questions