countryfan_nt
Well-known Member
- Joined
- May 19, 2004
- Messages
- 765
- Office Version
- 365
- Platform
- Windows
Hello friends, Hope all is well!
Is there a shorter version to make the below formula?
it suppose to give a 1, if any result showed up in a certain month, and provided that the amount related is above 4999.
1. Basically, the point is to show the number of months that had any activity or results above 4999.
2. The formula is too long, can it be shortened?
Thank you very much in advance!
Is there a shorter version to make the below formula?
it suppose to give a 1, if any result showed up in a certain month, and provided that the amount related is above 4999.
1. Basically, the point is to show the number of months that had any activity or results above 4999.
2. The formula is too long, can it be shortened?
Code:
=SUM(IF(COUNTIFS($B$4:$B$13,{"Jan"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Feb"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Mar"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Apr"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"May"},$C$4:$C$13,">4999")>0,1,0)*IF(COUNTIFS($B$4:$B$13,{"Jun"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Jul"},$C$4:$C$13,">4999")>0,1,0)*IF(COUNTIFS($B$4:$B$13,{"Aug"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Sep"},$C$4:$C$13,">4999")>0,1,0)+IF(COUNTIFS($B$4:$B$13,{"Oct"},$C$4:$C$13,">4999")>0,1,0))Thank you very much in advance!
Last edited:
