# Sum If Blank Cells

1. ## Sum If Blank Cells

Hi,

I need a formula that will sum up the total usage for all users of each report. For example, i need to formula in Column F to sum up Report 1's usage (Which would be 25 as there is only 1 user), and then sum up Report 2's usage (Which would be 65) etc. But what i also need is for numbers to only appear where the report name is. For example, for Report 2 i'd need the 65 to appear in D3, and then the rest of the cells be blank until D18 for Report 3's total usage, and then blank up until Report 4 etc.

img1.PNG

I've attached the example file.

I hope that all make sense, but let me know if not.

Many thanks,
swood  Register To Reply

2. ## Re: Sum If Blank Cells

Yep. In D2, copied down:

=IF(A2<>"",SUMPRODUCT((LOOKUP(ROW(\$A\$2:\$A\$48),ROW(\$A\$2:\$A\$48)/(\$A\$2:\$A\$48>0),\$A\$2:\$A\$48)=A2)*\$C\$2:\$C\$48),"")  Register To Reply

3. ## Re: Sum If Blank Cells Originally Posted by Glenn Kennedy Yep. In D2, copied down:

=IF(A2<>"",SUMPRODUCT((LOOKUP(ROW(\$A\$2:\$A\$48),ROW(\$A\$2:\$A\$48)/(\$A\$2:\$A\$48>0),\$A\$2:\$A\$48)=A2)*\$C\$2:\$C\$48),"")
That's exactly what i needed. Thanks so much for the quick reply  Register To Reply

4. ## Re: Sum If Blank Cells

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.  Register To Reply

5. ## Re: Sum If Blank Cells

Another way:

=IF(A2="","",SUM(INDEX(\$C\$1:C2,MATCH(1,(--(\$A\$1:A2<>"")))):INDEX(\$C\$1:\$C\$48,IFERROR(AGGREGATE(15,6,(ROW(A3:\$A\$48)/(A3:\$A\$48<>"")),1)-1,MATCH(1,(--(\$B\$1:\$B\$48<>"")))))))  Register To Reply

##### Users Browsing this Thread

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