Hello Excel guru's,
Can the following formula be adapted to run over a dynamic range. Currently it will only work if the range is fixed to those rows containing data, just the first 21 rows.
I'm using Sumproduct because it will be pulling data from a closed workbook.
=SUMPRODUCT(--('QO'!$J$12:J500="SQ"),--('QO'!$F$12:F500),--(MONTH('QO'!$E$12:E500)=MONTH(TODAY())))
Thank you
Last edited by sipa; 01-29-2019 at 07:17 AM.
So, you may find below variant will work as-is, pending data type in F (assumed to be numbers where numeric value)
Formula:
=SUMPRODUCT((('QO'!$J$12:J500="SQ")*(TEXT('QO'!$E$12:E500,"yyyymm")=TEXT(TODAY(),"yyyymm"))),'QO'!$F$12:F500)
that being said, it is always a good idea when using SUMPRODUCT to limit your range as much as possible, even more so when used in conjunction with Volatile functions [like TODAY() ]
If we assume Column J is always Text then I would suggest you create a Dynamic Named Range a) to avoid repetition in your calc, and b) succinctness... so, via Name Manager create following:
Formula:Name: =_Data
RefersTo: ='QO'!$A$12:INDEX('QO'!$J:$J,MAX(12,MATCH(REPT("Z",255),'QO'!$J:$J)))
then, modify SUMPRODUCT to:
Formula:=SUMPRODUCT(((INDEX(_Data,0,10)="SQ")*(TEXT(INDEX(_Data,0,5),"yyyymm")=TEXT(TODAY(),"yyyymm"))),INDEX(_Data,0,6))
the _Data range will expand / contract relative to last text entry found in Column J on 'QO' sheet
edit: on an aside, assuming your dates are stored as dates (where populated) you could use SUMIFS rather than SUMPRODUCT, this will be much more efficient.
If they were not actual dates then the MONTH function would not work in the existing formula.Originally Posted by XLent
=SUMIFS('QO'!$F$12:F500,'QO'!$J$12:J500,"SQ",'QO'!$E$12:E500,">"&EOMONTH(TODAY(),-1),'QO'!$E$12:E500,"<="&EOMONTH(TODAY(),0))
No, that's not correct. MONTH will explicitly coerce so if they're strings, rather than numbers, it will coerce and if valid date "strings" it will work.
The fact OP was stating they were getting an error implied, to me, that there are either values in the "date" or "sum" ranges that cannot be explicitly coerced.
The revised approach removes the explicit coercion from both elements -- TEXT will still work as expected for dates and/or dates stored as strings and will ignore other values (effectively).
A1: apple
A2: '29-Jan {i.e string}
A3: 29/1/2019
MONTH will generate errors on A1 and work for A2 & A3 whereas TEXT will work for all (i.e. FALSE for A1, TRUE for A2 & A3).
edit: I should have used a null string in my example as if formulae involved in the date/sum precedent ranges they will be responsible for the coercion errors (c/o --, MONTH, EOMONTH etc)
Last edited by XLent; 01-29-2019 at 08:19 AM.
Hi Jason,
Thanks for replying. The dates are actual dates.
Unfortunately Sumifs won't work on closed workbooks.
XLent,
Thanks for you reply.
Your formula revision is now working for me as expected, thank you for that.
I will also try and follow your advice and implement a Dynamic Named Range, I can see how that would be more efficient. I'll come back if I encounter any problems.HTML Code:
With thanks and regards
Corrected twice in one thread on things that I could have sworn I was right.
This is me keeping quiet now
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks