hLookup

[Chris3177]

I want excel to look up a certain value in a row and for each cell that has the value I want it to sum the corresponding numerical values.

For example:
C/R A B C D E F
1 Director CRA Mng CRA CRA Mng
2
3
4
5
6 1.4 1.3 1.0 .08 .98 .78

The formula should look up "CRA" in row 1 and then sum all of the corresponding values in row 6 i.e. 1.3+.08+.98 = 2.36

I tried a hlookup, but don't seem to know how to use the formula to look for multiple values of the same and then add them together???

Thank you!

 

[T. Valko]

I want excel to look up a certain value in a row and for each cell that has the value I want it to sum the corresponding numerical values.

For example:
C/R A B C D E F
1 Director CRA Mng CRA CRA Mng
2
3
4
5
6 1.4 1.3 1.0 .08 .98 .78

The formula should look up "CRA" in row 1 and then sum all of the corresponding values in row 6 i.e. 1.3+.08+.98 = 2.36

I tried a hlookup, but don't seem to know how to use the formula to look for multiple values of the same and then add them together???

Thank you!

Try this...

=SUMIF(A1:F1,"CRA",A6:F6)

 

[Chris3177]

Try this...

=SUMIF(A1:F1,"CRA",A6:F6)

Thank you! It seems so logical once I see the solution. Thank you very much you saved me a lot of manual entry!
Chris

 

[T. Valko]

Thank you! It seems so logical once I see the solution. Thank you very much you saved me a lot of manual entry!
Chris

You're welcome. Thanks for the feedback!

 

[Chris3177]

I guess I spoke to soon...

The cells i want to add are not in a range such as A6:F6 and there a multiple cells that need to be summed. Currently the formula is as you stated except for the last portion. The formula reads as:

=SUMIF(A1:F1,"CRA",A6,C6,F6,I6,L6,N6,Q6) and excel doesn't like this.

Thanks in advance!

 

[T. Valko]

I guess I spoke to soon...

The cells i want to add are not in a range such as A6:F6 and there a multiple cells that need to be summed. Currently the formula is as you stated except for the last portion. The formula reads as:

=SUMIF(A1:F1,"CRA",A6,C6,F6,I6,L6,N6,Q6) and excel doesn't like this.

Thanks in advance!

If the row that contains the criteria value "CRA" only goes to column F, A1:F1, then how can you relate the cells to sum I6,L6,N6,Q6 to the criteria "CRA"?

 

[Chris3177]

If the row that contains the criteria value "CRA" only goes to column F, A1:F1, then how can you relate the cells to sum I6,L6,N6,Q6 to the criteria "CRA"?

Sorry, the I forgot to update the first portion of the formula.

=SUMIF(A1:Q1,"CRA",A6,C6,F6,I6,L6,N6,Q6)

I hope that helps.

Thx

 

[T. Valko]

Sorry, the I forgot to update the first portion of the formula.

=SUMIF(A1:Q1,"CRA",A6,C6,F6,I6,L6,N6,Q6)

I hope that helps.

Thx

Why can't you just use:

=SUMIF(A1:Q1,"CRA",A6:Q6)

 

[Chris3177]

Why can't you just use:

=SUMIF(A1:Q1,"CRA",A6:Q6)

The individual cells reference are sums of the cells in between. In my actual spreadsheet there are multiple cells in between. For example I am trying to look at cells:
C6,H6,M6... These cells are the sum of the cells in between. For example the data in cell C6 is the sum of the data in A6:B6, H6 is the sum of D6:G6 etc.

 

[T. Valko]

The individual cells reference are sums of the cells in between. In my actual spreadsheet there are multiple cells in between. For example I am trying to look at cells:
C6,H6,M6... These cells are the sum of the cells in between. For example the data in cell C6 is the sum of the data in A6:B6, H6 is the sum of D6:G6 etc.

Well, at this point I'm confused and would need to see the setup with the data.

Can you post a sample file? You can use a free file host if need be.