Excel arrey formulas

  steven_frost 12:28 13 May 2005

i'm having a problem with this formula any ideas

=IF('Utility, depreciation, lights '!I:I="MT-South",and'Utility, depreciation, lights '!N:N="F0800")SUM('Utility, depreciation, lights '!S:S)

  VoG II 21:41 13 May 2005

Please can you explain what the formula is supposed to do. The syntax is

IF(AND(condition1, condition2), Do something, Do something else)

  steven_frost 07:25 14 May 2005

What it is ment to do is look at one sheet for with say a code number ie f0200 then look ofr the figures and add them up then show it on a summary sheet

  VoG II 07:57 14 May 2005

If you wany to count the number of values equal to "f0200" then you could use

=SUMIF(A1:A100, "f0200")

However, if you want to count the number of rows where several conditions are met in different columns then you need to use SUMPRODUCT

For example

=SUMPRODUCT((B2:B7="A") * (C2:C7="A"))

will count the number of instances where "A" occurs in BOTH columns B and C.

=SUMPRODUCT((B2:B7="A") + (C2:C7="C"))

will count the number of rows where column B contains A and column C contains C.

  steven_frost 08:28 14 May 2005

the problem is that these figures are on one sheet and i need for the total to be displaid on a sumarry sheet so linking the two

  VoG II 08:31 14 May 2005

On the summary sheet you would enter a formula like:

=SUMPRODUCT(('sheet1'!B2:B7="A") * ('sheet2'!C2:C7="A"))

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Indie publisher Canongate’s top 10 book covers of 2017

New iMac Pro release date, UK price & specs rumours

Tablettes Amazon Fire : quel modèle choisir ?