Excel Analysis problem 1

[JamesMichell]

In Excel I am using the INDEX function to find the value in a table at the junction of a column and a row. That works fine and gives me two values which are collected in columns A and B. Column C consists of a list of monetary values. I then need to collect the total of values in Column C where specified values appear in columns A and B. For example, in plain English, I need to find the total of the values in column C where the value in A is 050 and the value in B is 31700.
To do this I use the conditional SUM function with nested IF statements as follows: {=SUM(IF(A1:A10=050,IF(B1:B10=31700,C1:C10)}. However this does not work since it appears that Excel is only seeing the formulas in columns A and B and not the values. I can test this by writing in the values directly into A and B and then everything is fine. Interestingly the SUMIF function does work OK but it only allows you one parameter and I need to use two. I have tried using different formats on the cells with no success.

Anybody got any ideas on this?

 

[GlennUK]

Hi JamesMichell,

if your entries are being treated as text you will need to put quotes around your search values, like this ...

{=SUM(IF(A1:A10="050",IF(B1:B10="31700",C1:C10)))}

Hope that works.
Regards Glenn.

 

[xlhelp]

From my post a few days ago. thread68-293073.

=SUM(IF((A1:A10=050)*(B1:B10=31700),C1:C10,0))

I am sure you are aware that this is an array formula. (CTRL/SHIFT/ENTER)

HTH

Indu

 

[JamesMichell]

Thanks, Glenn, and I did try the quotation marks to denote text but it makes no difference to the problem.

And Indu, also. Your formula works just fine as long as I input the values directly into columns A and B. But when I put in the Index formulas, excel sees just those, and not the numeric results!

But thanks to both for your help.........

James

 

[xlhelp]

I would love to see this spreadsheet, if you have no problems sending it.

xlwrdhlp@clarica.com

INdu

 

[JamesMichell]

Indu, no problem. Just give me a bit of time to construct a subsection of it for you and I'll email it accross.


James

 

[JamesMichell]

Indu

Many thanks for final the solution you found for me! As you discovered the problem was that the figures in the cells that were being called by the formulas had trailing spaces after them(imported from a text file)so of course excel didn't recognise them. The solution was to use the TRIM function to take out these spaces and then the whole query worked perfectly.

Thanks again

James