Dear all,
Good day,
I want to use sumproduct formula to sum all containers in December and November as below. But unfortunely, sometimes column B or C contains text , it makes our result become #value. In this case, my desired result should be 15. Is there any way to ignore the text ? Kindly help me to solve it
Thanks
\1
Hi minhtien1900- paste this ARRAY FORMULA in B11, copied across and down:*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
Please Login or Register to view this content.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
Last edited by leelnich; 12-26-2017 at 11:36 AM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
You can use this version which doesn't require "array entry"
=SUMPRODUCT((MONTH($D$2:$D$5)=B$10)*ISNUMBER($B$2:$C$5),$B$2:$C$5)
Audere est facere
Here is another array formula
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks