Problems running SUMIF on a haystack of formulae

[Zukovsky]

Hi all,

I have a small problem and was looking for some help. Running Excel 2003.

I have a sumif(needle, haystack, values) statement, where the needle is a long number, and the list of values in the haystack contains the needle. Yet the sumif does not retrieve the value for the needle in the haystack!

ie. the needle is 112212397, and the haystack contains 112212397 with a value of £27,000. Yet, the statement returns £0.00. Breaking this down in the analyzer, it comes up with this:
sumif(needle, #VALUE, values)

In my experience, this usually means that there is a problem with the haystack. So looking at my haystack now, I can only see one problem - and that is that the 112212397 number in the haystack is actually =CONCATENATE((100+C3),(200+D3),(300+E3)).

Is the fact that the haystack is a list of formulae messing up the SUMIF statement, and if so - how do I go about putting it right?

Thanks in advance,

Martin

[westconn1]

number in the haystack is actually =CONCATENATE((100+C3),(200+D3),(300+E3)).

i doubt that is the problem, your sumif formula must be wrong

post the full formula, and the contents of relevant cells

[Zukovsky]

OK, the sumif is as follows:

=SUMIF(P100,Calculations!$F$3:$F$129,Calculations!$G$3:$G$129)

where:

P100 is: 106212397

Calculations!$F$3 is: =CONCATENATE((100+C3),(200+D3),(300+E3))
C3 is 6
D3 is 12
E3 is 97

Calculations!$G$3 is: £23,742.40


The result is £0.00

[westconn1]

i think for what you are trying to do you try
if( p100= Calculations!$F$3:$F$129,sum(Calculations!$G$3:$G$129),"")

[Zukovsky]

Thanks for the answer, westconn1

I tried what you suggested, but it just gives me a #Value error.

Not sure i follow the logic of it either. What i'm trying to do is search for the value of P100 in the list of Calculations!$F$3:$F$129, and when it finds a match, to add the value from Calculations!$G$3:$G$129 with the same index. I'm quite certain I'm doing it right, in terms of sumif.

I'm going to try doing it the long-winded way with OFFSET, MATCH, and INDEX to see if that will resolve the issue.

[Zukovsky]

OK,

problem solved. Don't know why I didn't think of doing this yesterday, but I put in an OFFSET and a MATCH like so:

=OFFSET(Calculations!$G$3,(MATCH(Permutations!J100,Calculations!$F$3:$F$129,0)-1),0)

Thanks for your help. I'll drink more coffee next time before asking.