LinkBack URL
About LinkBacks
Hi Community!
I am updating a worksheet at work that I am having trouble with - please see attached.
The "Summery" tab is drawing from "Rollforward" but all formulas are returning a #VALUE.
Please help! Thank you so much.
SUMPRODUCT formula showing #VALUE
Hi Community!
I am updating a worksheet at work that I am having trouble with - please see attached.
The "Summery" tab is drawing from "Rollforward" but all formulas are returning a #VALUE.
Please help! Thank you so much.
When you are using sumproduct, you will get an error when you try to multiple text times a number. For example, in D9 of Summary
=SUMPRODUCT((Rollforward!$A$9:$A$813=Summary!$A$7)*(Rollforward!$D$9:$D$813=Summary!B9)*(Rollforward!$I$9:$I$813))
The first part of that equation ((Rollforward!$A$9:$A$813=Summary!$A$7)*(Rollforward!$D$9:$D$813=Summary!B9)) will always give a number (0 or 1). The second part
*(Rollforward!$I$9:$I$813) is sometimes text. To fix this, set that last part as a separate argument (which is really the way SUMPRODUCT was set up to operate). Therefore, this equation becomes
=SUMPRODUCT((Rollforward!$A$9:$A$813=Summary!$A$7)*(Rollforward!$D$9:$D$813=Summary!B9), (Rollforward!$I$9:$I$813))
Hope that helps.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
For this formula in D9
=SUMPRODUCT((Rollforward!$A$9:$A$813=Summary!$A$7)*(Rollforward!$D$9:$D$813=Summary!B9)*(Rollforward!$I$9:$I$813))
You have a stray space in a cell that appears empty in Rollforward!I292
That is technically a TEXT string, and Number*Text = #Value!
Clear the contents of Rollforward!I292, and the formulas in Summary columns D and E work fine.
I imaging similar issue for the other 'sets' of formulas in G:H etc..
Try it like this in D9:
=SUMPRODUCT((Rollforward!$A$9:$A$813=$A$7)*(Rollforward!$D$9:$D$813=B9),Rollforward!$I$9:$I$813)
and like this in G9:
=SUMPRODUCT((Rollforward!$A$9:$A$813=$A$7)*(Rollforward!$D$9:$D$813=B9),Rollforward!$Z$9:$Z$813)
then copy down.
Hope this helps.
Pete
Thank you all so much! It worked!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks