Hey there,
I have this formula which is counting the number of cells in a column that fall within each calender month.
However, if there is a formula at the bottom of column B and C that yield a "", the formula breaks.
In my workbook, B/C:133 have a formula =""
I will need the formula in column E to work if there is a formula that yields a "" in column B and C.
I really appreciate your help!
workbook: BOOK 6.xlsx
Try this array formula in col E
=SUMPRODUCT(--(B$2:B$133<>""),--(IFERROR(MONTH(B$2:B$133),0)=D2))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Copy down
Life's a spreadsheet, Excel!
Say thanks, Click *
Awesome work Ace. I appreciate it.
Here's another one...
=SUMPRODUCT(--(TEXT(B$2:B$133,"mmm")=TEXT(D2*29,"mmm")))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
What if I wanted to find the average of values in column C that are associated with each month?
Again, there will be a formula at the bottom of cells C and B that will return a blank value =""
Try this array formula**:
=IFERROR(AVERAGE(IF(TEXT(B$2:B$133,"mmm")=TEXT(D2*29,"mmm"),C$2:C$133)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down as needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks