Excel arrey formulas

  steven_frost 12:28 13 May 05

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 05

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 05

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 05

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 05

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 05

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.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…