# Single cell array or sumproduct formula

1. ## Single cell array or sumproduct formula

To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year.

The sample attached explains it better.

Thanks for any ideas.

2. ## Re: Single cell array or sumproduct formula

Please be more specific, what exactly are you wanting accomplished? A sum of the products of the percentages (until March) and what? 20000?

3. ## Re: Single cell array or sumproduct formula

Each month has the potential to incur a penalty. I'm trying to sum up the penalties for the year. Effectively this means every month from January thru December.

4. ## Re: Single cell array or sumproduct formula

I assume that over 90% incurs no penalty, try this formula for the whole year

=SUMPRODUCT(--(F11:Q11<>""),LOOKUP(F11:Q11,{0,0.8,0.850001,0.900001;30000,20000,10000,0}))

5. ## Re: Single cell array or sumproduct formula

Thanks - works perfectly. i was missing the <>"" check.

Much appreciated

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