Formula Problem

Posted by Jenny Conte on January 24, 2001 6:28 AM

I think I'm almost there, but the following fomula is giving me a result of 0 which is not correct, but I suppose it is better than a complete error message!

=SUMIF('Costs 1'!$B$39:$B$44,NOT(B5:B23),'Costs 2'!$C$39:$C$44)

I have created a workbook and on my Totals worksheet I want the formula to look at a range of cells (C39:C44) on several worksheets(Costs 1 and Costs 2 for example). This range contains a list of suppliers.

Within this range if it finds a supplier name that does not appear in the main supplier list on my totals page (B5:B23), I want it to return the corresponding figure in the range C39:C44 on each of the worsheets and add them up. This figure will then give me the total spend with suppliers other than the core ones on my list.

Can anyone tell my why this isn't working, as there are definitely figures in the worksheets that the formula should be pulling through?

Thanks in advance.

Posted by Mark W. on January 24, 2001 6:48 AM

{=SUM((B5:B23&LT;&GT;'Costs 1'!$B$39:$B$44)*'Costs 2'!$C$39:$C$44)}

This is an array formula which must be entered using the
Shift+Ctrl+Enter key combination

Posted by Mark W. on January 24, 2001 8:25 AM

Jenny, before posting my response (below) I failed
to notice that your supplier name list was longer
than the compare list on worksheet, Cost 1. Try
this instead:

{=SUM(ISNA(MATCH('Costs 1'!$B$39:$B$44,B5:B23,0))*'Costs 2'!$C$39:$C$44)}

Posted by Jenny on January 25, 2001 2:36 AM

Thanks for your quick response. Unfortunately I am still receiving a value of 0, so it is not bringing through the costs that I want it to

Any other ideas?

Thanks
Jenny

Posted by Mark W. on January 25, 2001 7:38 AM

Jenny, what's the data type of your costs? Numeric or text?

Posted by Jenny Conte on January 25, 2001 7:54 AM

B39:B44 is text (General), B5:B23 is text (General) and C39:C44 is numeric.

Thanks.

Posted by Mark W. on January 25, 2001 8:02 AM

Jenny, what's the result of the following formula:

=IF(AND(ISNUMBER(C39:C44)),"yep","nope")

Also, did you check for and remove any and all trailing spaces from your text values?

Posted by Jenny on January 25, 2001 8:28 AM

The result is Nope.
There are no trailing spaces after the text values.

By the way I am using Excel 97.

Thanks.

Posted by Mark W. on January 25, 2001 8:38 AM

Nope" means that some if not all of your numbers are really text. The SUM() function will return 0 when you sum text. I know, I know... they look like numbers, but trust me they're text.

Posted by Jenny on January 25, 2001 10:29 AM

It's working now. Thank you so much.

Jenny