# Getting an array to ignore blank cells

1. ## Getting an array to ignore blank cells

I was given the following array formula "=SUM(--(D\$3:D\$117>=E\$3:E\$117))" to use on my spread sheet. The formula works well, however, there are blank cells with in the coloum which i can't delete but would like the array formula to ignore when it runs? Any help would be muchly appreciated.

Michael

2. ## Re: Getting an array to ignore blank cells

hi Mike001, try:
=SUMPRODUCT((D\$3:D\$117>=E\$3:E\$117)*(D\$3:D\$117<>"")*(E\$3:E\$117<>""))

SUMPRODUCT works a little faster than an SUM array

3. ## Re: Getting an array to ignore blank cells

Originally Posted by benishiryo
hi Mike001, try:
=SUMPRODUCT((D\$3:D\$117>=E\$3:E\$117)*(D\$3:D\$117<>"")*(E\$3:E\$117<>""))

SUMPRODUCT works a little faster than an SUM array
Hi there, thank you very much this did work. Do you know if this would work for calculating the same thing in a row instead of a coloum?

4. ## Re: Getting an array to ignore blank cells

Originally Posted by benishiryo
http://www.excelforum.com/excel-form...ank-cells.html

hi there. why not try it out? =)
it's hard to know exactly what you need without seeing the file. if you face problems, upload a sample excel file in the thread. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon. ideally, it should contain your desired results

if you're satisfied with the answer, please mark it as "Solved". my signature in the thread will guide you how to do that. thanks
I have attached the workbook as I can't see how the same formula would work. If you wouldn't mind having a look for me that would be great.

5. ## Re: Getting an array to ignore blank cells

You want to get the SUM of Actual-Sum of Target for each person (each row)?
Try this for first row and copy down:
=SUMIF(\$D\$2:\$AQ\$2,D\$2,\$D3:\$AQ3)-SUMIF(\$D\$2:\$AQ\$2,E\$2,\$D3:\$AQ3)

6. ## Re: Getting an array to ignore blank cells

Originally Posted by bebo021999
You want to get the SUM of Actual-Sum of Target for each person (each row)?
Try this for first row and copy down:
=SUMIF(\$D\$2:\$AQ\$2,D\$2,\$D3:\$AQ3)-SUMIF(\$D\$2:\$AQ\$2,E\$2,\$D3:\$AQ3)
I am trying to count how many of the colored cells are a certain color eg. No of cells colored - Green, Amber & Red

7. ## Re: Getting an array to ignore blank cells

Hi Mike,

Is this not what you have in rows 120, 121 and 122?

8. ## Re: Getting an array to ignore blank cells

Originally Posted by djapigo
Hi Mike,

Is this not what you have in rows 120, 121 and 122?
Yes, however, I need a formula at the end of each row eg. end of row 3

9. ## Re: Getting an array to ignore blank cells

Originally Posted by djapigo
Hi Mike,

Is this not what you have in rows 120, 121 and 122?
Yes, however, I need a formula at the end of each row eg. end of row 3

10. ## Re: Getting an array to ignore blank cells

Mike...

I've revised my formulas... it used to be really ugly...

AT3: =SUMPRODUCT((\$D\$2:\$AP\$2="Actual")*(D3:AP3>=E3:AQ3)*(D3:AP3>0))

AU3: =SUMPRODUCT((\$D\$2:\$AP\$2="Actual")*(D3:AP3<E3:AQ3)*(D3:AP3>0))-AV3

AV3: =SUMPRODUCT((\$D\$2:\$AP\$2="Actual")*(D3:AP3<E3:AQ3-6)*(D3:AP3>0))

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