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?

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.

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

Hi Mike,

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

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

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