Formula does not take into account blank cells, and when I attempt to turn it into a percentage, it becomes greater than 100% if there is a blank cell.
Formula is as follows:
=SUMPRODUCT(('All Data sheet '!D$13:IV$13>=Print!$E$3)*('All Data sheet '!D$13:IV$13<=Print!$E$4)*('All Data sheet '!D14:IV14='All Data sheet '!$D$3))/SUMPRODUCT(('All Data sheet '!D$13:IV$13>=Print!$E$3)*('All Data sheet '!D$13:IV$13<=Print!$E$4))
What am I missing to omit the blank cells?
Last edited by seanrigby; 10-27-2009 at 12:46 AM.
Hi
If I've understood correctly just introduce an extra test in both the numerator and denominator. viz.
HTH
*(All Data sheet '!D$13:IV$13<>"")
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I did this formula as suggested...
=SUMPRODUCT(('All Data sheet '!D$13:IV$13>=Print!$E$3)*('All Data sheet '!D$13:IV$13<=Print!$E$4)*('All Data sheet '!D21:IV21='All Data sheet '!$D$3*('All Data sheet '!$D$12:$IV$12=$L$1)*('All Data sheet '!D21:IV21<>""))/SUMPRODUCT(('All Data sheet '!D$13:IV$13>=Print!$E$3)*('All Data sheet '!D$13:IV$13<=Print!$E$4)*('All Data sheet '!$D$12:$IV$12=$L$1)*('All Data sheet '!D21:IV21<>"")))
Still comes up with the same results which is > 100%
the formula broken down is as follows:
('All Data sheet '!D$13:IV$13>=Print!$E$3) - greater than a specific date
('All Data sheet '!D$13:IV$13<=Print!$E$4) - Less than a specific date
('All Data sheet '!D21:IV21='All Data sheet '!$D$3 - equal to a number
('All Data sheet '!$D$12:$IV$12=$L$1) - equal to a name
('All Data sheet '!D21:IV21<>"") - greater than a blank
Last edited by seanrigby; 10-27-2009 at 12:15 AM.
I figured out a different way to do it. Thanks a lot for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks