# Find a text and return the sum of values in adjacent cells matching the text in range

1. ## Find a text and return the sum of values in adjacent cells matching the text in range

I have a contractors that provide services under Normal categories and Special categories.
The log is kept for their services. Anytime a contractor works, an entry is made into the log for hours worked and type of work.

I have values as shown below in columns A to J (as a sample). The actual data extends a couple hundred columns.

A B C D E F G H I J
1 N 0.5 N 0.5 S 2 S 2 N

It shows that the contractor provided "Normal" services 3 times and "Special" services 2 times.
I am looking for a formula that will calculate the SUM of hours spent under each category.

E.g for Normal(N) , the formula should return 3.5
i.e find the instances of N and add the value in the cell to the left of it.

NO VBA pls.
Any way to do it by just using functions??

2. ## Re: Find a text and return the sum of values in adjacent cells matching the text in range

Try this:

=SUMIF(B1:J1,"N",A1:I1)

Note that the two ranges are the same size, but offset by one column - adjust the ranges to suit your data.

Hope this helps.

Pete

3. ## Re: Find a text and return the sum of values in adjacent cells matching the text in range

Thanks Pete.

This is very helpful. I had a few more columns in between that I had not mentioned in the question. But I adjusted the formula and it solved the need.

There are currently 1 users browsing this thread. (0 members and 1 guests)