Simplifying my formula

Do you enter this formula as array (i-e by pressing Ctrl+Shift+Enter)?

Originally Posted by lordfa9

Hi all

i currently have this code for my excel spreadsheet it is to count the instances where i want to find out how many types of red apples i have, so i wrote the following function.

]

this is where column B is the type of fruit and column I is the fruit's colour

so i was wondering if there is a way to simplify the fomula where i define the various ranges so that i can simplify the fomula to something like

]
where A=RD!B2:RD!B590 and B=RD!I2:RD!I590

i tried using the Define->name function (where i set the range as the name) but i couldnt get it to work.

Any suggestions?

Try Sumproduct():

=SUMPRODUCT(--(TRIM(A)="Apples"),--(TRIM(B)="Red"))

Originally Posted by lordfa9

ok so what i did was to 1st define the name of the range. So i went to the name->define and set the following as I
=Fruits!$I$2:Fruits!$I$590

The i used the following formula:
=SUM(TRIM(I)="Red")

after typing i set it as an array using Ctrl+Shift+Enter

The answer the formula gave me is 0 even though there is data in the worksheet that meets the criteria.

I tried using the SUMPRODUCT function and still it gave me 0

Please attach a sample file.

somehow i managed to get it to work when i undid everything and tried again.

Not sure why though might be some typos in the formula, most likely the brackets