I have the below formula, which counts all the dates that fall on month,year (AA4&Z4). And it works...
Please Login or Register to view this content.
Where and how do I add, count only the ones that fall on month, year (AA4&Z4) AND that are 'ACTIVE' under Name!$G$56:$G$155
Last edited by clprdctn; 07-27-2016 at 11:03 AM.
TRY
=SUMPRODUCT(--(TEXT(Name!$BC$56:$BC$155,"yyyym")=AA4&Z4))*(Name!$G$56:$G$155="Active"))
It didn't work as it took the "IF and =0,NA()" function away.
You don't need the IF .....
Or if you want it simply replace the SUMPRODUCT statements.
The reason I kept is to allow the formula to display #N/A if the value is 0 so that it doesn't show up on the chart.
=IF(SUMPRODUCT(--(TEXT(Name!$BC$56:$BC$155,"yyyym")=AA4&Z4))*(Name!$G$56:$G$155="Active"))=0,NA(),SUMPRODUCT(--(TEXT(Name!$BC$56:$BC$155,"yyyym")=AA4&Z4))*(Name!$G$56:$G$155="Active")))
Try this...
=IFERROR(1/(1/SUMPRODUCT(--(TEXT(Name!$BC$56:$BC$155,"yyyym")=AA4&Z4),--(Name!$G$56:$G$155="Active"))),NA())
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you John, Thank you Tony. Tony yours worked great.
You're welcome. Thanks for the feedback!
One last question:
Any idea how can I SUM multiple of those formulas?
=IFERROR(1/(1/SUMPRODUCT(--(TEXT(Name!$FP$56:$FP$155,"yyyym")=AA4&Z4),--(Name!$G$56:$G$155="Active"))),NA())
+
=IFERROR(1/(1/SUMPRODUCT(--(TEXT(Name!$FY$56:$FY$155,"yyyym")=AA4&Z4),--(Name!$G$56:$G$155="Active"))),NA())
How about putting each formula in a separate cell then sum those cells together?
You'll have to account for the possibility that one or both formulas return #N/A.
=SUMIF(A1:A2,"<>#N/A")
If possible, it'd be great to have it in one cell. I tried the following but still a no go...
Please Login or Register to view this content.
Like this...
=IFERROR(1/(1/(SUMPRODUCT(...)+SUMPRODUCT(...))),"N/A")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks