# Why doesn't this array function work?

1. ## Why doesn't this array function work?

I have the following table:

``Please Login or Register  to view this content.``
The following formula returns 13 as I expect it to.
``Please Login or Register  to view this content.``
The following formula returns 0 when I expect it to return 11. Why doesn't it return 11?
``Please Login or Register  to view this content.``

2. ## Re: Why doesn't this array function work?

Did you use CTRL-SHIFT-ENTER to commit the formula, instead of the usual ENTER ?

Hope this helps.

Pete

3. ## Re: Why doesn't this array function work?

=SUMPRODUCT((A2:A7="Olof")*(B2:B7="Ice Cream")*(C2:C7))

wORKS FOR YOU?

4. ## Re: Why doesn't this array function work?

Actually, you can't use AND in that way with array formulae - you need to do it like this:

=SUM(IF((A2:A7="Olof")*(B2:B7="Ice Cream");C2:C7;0))

Here the * is equivalent to AND.

Use CSE to commit.

Hope this helps.

Pete

5. ## Re: Why doesn't this array function work?

Hi OldManExcellor,

Instead of AND function, use logic as (A2:A7="Olof")*(B2:B7="Ice Cream")

Regards,
DILIPandey
<click on below * if this helps>

6. ## Re: Why doesn't this array function work?

Alternatively, use the SUMIFS function that allows for more than one criteria to be used to determine what is summed.

Formula:
`Please Login or Register  to view this content.`

7. ## Re: Why doesn't this array function work?

SUMIFS is not available in XL 2003.

Pete

8. ## Re: Why doesn't this array function work?

Originally Posted by newdoverman
Alternatively, use the SUMIFS function that allows for more than one criteria to be used to determine what is summed.
.....
The OP has 2003 according to his/her profile SUMIFS() is for 2007 and above.

[EDIT].
You beat me to it Pete!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1