Hi,
Any help would be really appreciated on this one.
I have a spreadsheet with two workbooks and I am trying to lookup a value from Sheet2 in Sheet1 and then sum the value of certain columns only if they all have a value.
I can get the formula to calculate the columns I want but I cant incorporate it to ignore if all columns are not populated
Current formula
{=SUM(VLOOKUP(B4,Sheet1!$B$4:$X$44,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22},FALSE))}
Thanks
Paul
Let's see if we understand this.
If Dn:Un contains less than 20 numbers then you want the formula to return a blank?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Tony,
Yes that is exactly what I need.
Thanks
Paul
Try this array formula**:
=IF(COUNT(INDEX(Sheet1!$D$4:$U$44,MATCH(B4,Sheet1!$B$4:$B$44,0),0))<20,"",SUM(VLOOKUP(B4,Sheet1!$B$4:$X$44,{3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22},0)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thank you so much Tony that has worked a treat.
Regards
Paul
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks