Hi there!
i feel that this formula can be way more simple... can you guys help me with this?
=SUMPRODUCT((_1=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_2=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_3=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_4=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_5=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_6=BM4)*(_FC<=CP2)*(_FC>=CP1)+(_7=BM4)*(_FC<=CP2)*(_FC>=CP1))
(_FC<=CP2)*(_FC>=CP1) = are the days of a month (january for instance)
_1 to _7 are ranges
BM4 is a constant
I would greatly appreciate your help
Hi, try this.
If you want to count BM4 only one time for a row, try this
=SUMPRODUCT((_FC>=CP1)*(_FC<=CP2)*COUNTIF(BM4,CHOOSE({1,2,3,4,5,6,7},_1,_2,_3,_4,_5,_6,_7)))
=SUMPRODUCT((_FC>=CP1)*(_FC<=CP2)*(MMULT(COUNTIF(BM4,CHOOSE({1,2,3,4,5,6,7},_1,_2,_3,_4,_5,_6,_7)),{1;2;3;4;5;6;7})>0))
Last edited by Haseeb Avarakkan; 06-22-2012 at 10:04 PM. Reason: Typo
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks