How to sum the rows cells with a value coressponding?

[Dhanola]

Hey, there I have an excel file in which I want to sum the value of a row cell corresponding to a condition.

see the below image:

https://ibb.co/55BRmXr

So in the above excel sheet, you can see that the row selected has a cell written: "gravity recovered cell".

My question is that from this row I want to sum all those cells which are corresponding to the cell containing the string "garvit recovered cell" and store the sum in the last amount column.

SO FROM THE ABOVE SELECTED ROWS G602 AND K602 WILL SUM UP ONLY AS THEIR LEFT-HAND SIDE CELLS HAVE "GARVIT RECOVERED" WRITTEN AND STORE 320 AS THE TOTAL IN CELL Q602

I am attaching the excel file so that you can get a good understanding.

link:

https://easyupload.io/h6tafx

Thanks & regards

Rahul Dhanola

[#2]

@Dhanola 

But column Q already contains another formula...

[#3]

@Hans Vogelaar 

Thanks for responding. I have solved the problem and copied this formula in each week's cell and autofill the remaining cells just above total of each week.

=SUM(SUMIFS(C10:O10,B10:N10,{"*GARVIT RECOVERED CELL*"}))

but I have another query

I have an excel sheet in which there is a column that has all cells with either of the two formulas one is the sum, and the other is sumif(). My question is how to color those cells only which contain the sum() function as the outer function?

https://preview.redd.it/jmhnxm3xj1z91.png?width=1366&format=png&auto=webp&s=56b03ef5aa39f3c51c5d29c7...

as you can see in the above excel sheet I have selected column R and in this, I want to color only the cells which are between the cells written "amount" and the green colored cell in which sumifs() are used and the rest white cells with sum as outer function.

https://preview.redd.it/621ehpjlk1z91.png?width=1366&format=png&auto=webp&s=25a21c79a0fa48c12eb86a4c...

so like in the above image I want to color the cells like above for each week and for each month?

please check it on the excel sheet I am sharing here:

https://easyupload.io/vyvzeu

[#4]

@Dhanola 

Select R5:R701. R5 should be the active cell in the selection.

On the Home tab of the ribbon. select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=ISNUMBER(FIND("SUMIF",FORMULATEXT(R5)))

Click Format...

Activate the Fill tab.

Select light green.

Click OK, then click OK again.

[#5]

@Dhanola 

If you must ask the same question in different forums, you should always mention that explicitly, with a link to the other posts. Otherwise users might work on your problem in vain because it has already been solved elsewhere.

color the cell with formula given? 

[#6]

Ok i will ask only at one forum and share the forum where I asked from next time.

[#7 · Accepted solution]

hey, thanks for helping but I want one last help: if you can add one more condition in it i.e. if a cell has the function sumifs and also the value in the cell is greater than 0 then color it. I mean both conditions must be met then only it color else not.

Edit: hey I think i got the solution in conditional formatting with and function: =AND(R1>0, LEFT(FORMULATEXT(R1),7)="=sumifs") #or any function which is in a cell other than sumif can be checked just changing the text and replacing 7 with the formula text length.