Search word then add cell

[sirdon]

Hi,

See attached my sample. If you go to column V and X you will see in red font the outcome I am looking for.

I need to find the word and then sum cells. I hope this makes sense. Please let me know if you need further information.

Thanks,

[AliGW]

For the first one:

=SUMIFS(OFFSET($B7:$U7,0,2),$B$6:$U$6,"Emolument")

and the second I'm afraid I don't understand - there does not appear to be any summable data for anyone in their first column on row 17.

[jason.b75]

See attached my sample. If you go to column V and X you will see in red font the outcome I am looking for.

Having read that, I was expecting you to want a fomula to go in columns V and X, and that the red font would show some expected results that you had calculated manually. The additional decriptions could have just as easily been given here.

Ali's suggestion makes unnecessary use of a volatile function, it can be done without by defining the ranges correctly.

=SUMIFS($D7:$S7,$B$6:$Q$6,"Emolument")

I agree with Ali's comment that the second requirement makes no sense. Had you entered the expected results for the first couple of people as your expected outcome then maybe it would have been clearer.

[AliGW]

Yes, that's better. Funny - I tried it and it wasn't working, hence the OFFSET suggestion. Must have been a glitch at my end, as it's working fine now.

Sirdon - use Jason's version of the formula.

[sirdon]

AliGW, thank you so much. it all works. From the formula you gave i was able to do the other one. Many thanks as always

[AliGW]

Glad to have helped.