Good evening guys,
So this works if all the cells have data, but if blank, I get a #N/A error.
=SUMPRODUCT((H8:H11)*(L8:L11)*(G8:G11="SA450/60"))
As data will be added during the month the table starts off fairly sparse so I'd like my formula to skip/ignore if left blank.
Tried a few variants I've found online but no success.
Thanks in advance.
Welcome to the forum.
Which version of Excel do you have? Please update your forum profile.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi,
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
If I understand you correct - you just need to wrap your formula with an IFERROR function:
=IFERROR(SUMPRODUCT((H8:H11)*(L8:L11)*(G8:G11="SA450/60")),0)
This will return a 0
=IFERROR(SUMPRODUCT((H8:H11)*(L8:L11)*(G8:G11="SA450/60")),"")
This will return blank cell)
It is not known which column will contain blank cells.
If blank cells appear in Column H
=SUMPRODUCT((H8:H11)*(H8:H11<>"")*(L8:L11)*(G8:G11="SA450/60"))
If blank cells appear in Column H and L
=SUMPRODUCT((H8:H11)*(H8:H11<>"")*(L8:L11)*(L8:L11<>"")*(G8:G11="SA450/60"))
If blank cells appear in Column H , L and G
=SUMPRODUCT((H8:H11)*(H8:H11<>"")*(L8:L11)*(L8:L11<>"")*(G8:G11="SA450/60")*(G8:G11<>""))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks