zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 589
- Office Version
- 365
- Platform
- Windows
Hello all!
I have been struggling with this ridiculous formula for longer than I even want to admit, so now I'm here begging for help.
I'll try to explain this as clearly as I can:
What I have to work with:
What I'm trying to do:
That's all there is to it! (I do realize this is insane, but then, I work for crazy people, so there's that...)
I included the formulas that I currently have working so that, if you see that there's an easier way you can tell me and so that you can see what's there now in case that helps you to visualize this mess, LOL!
Can all of this even be done? The co-worker originally thought about a macro, but I got to thinking that having dynamic formulas on the Totals sheet would have the workbook be up to the minute at all times. If it would be easier with a macro, that's fine, too.
Thank you for even reading this novel!
Jenny
I have been struggling with this ridiculous formula for longer than I even want to admit, so now I'm here begging for help.
I'll try to explain this as clearly as I can:
What I have to work with:
- Sheet 1 is called "Totals"
- Sheet 2 is called "COMPLETE"
- Totals has a table on it where data from COMPLETE needs to be organized. The table is B4:N10.
- On Totals the year is in C2 and the months - from AUG-JUL - are in C3-N3.
- On Totals, O2 contains a formula that gives the last row number on COMPLETE that contains data. Also, there will be a varying number of rows at any given time as data is added or deleted.
- On COMPLETE, the columns we're concerned with are A, F, L and Q.
- Column A contains a date which could be varying months, date or year and is formatted as mm/dd/yyyy
- The other columns are just varying data
What I'm trying to do:
- On Totals, from C4-N4 counts how many rows on COMPLETE>column A are within the month and year from combining Totals C3-N3.
- I have this working with this formula: SUM(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0" & (MONTH(C$3&1))&"/"&$C2),1,0))
- ALL other formulas will relate to only the rows on COMPLETE where A is for the particular month. (C5 refers to the month represented by C3/C2 = 08/2017).
- So, C5 only involves rows on COMPLETE where A = any day in 08/2017.
- It needs to count how many of those rows have "ALLOCATION" in column F on COMPLETE.
- So, D5 only involves rows on COMPLETE where A = any day in 09/2017.
- It needs to count how many of those rows have "ALLOCATION" in column F on COMPLETE...
- I have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="ALLOCATION"))
- Now, the first problem -
- Here's what C6 on Totals needs to count on COMPLETE:
- If Q is NOT the word YES and is NOT blank
- If L is either the word NO or is colored red, but has NOT already been counted because of the data in Q
- I do NOT have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(--(IF(NOT(TEXT(INDIRECT("COMPLETE!$Q$2:$Q$"&$O$2),"@")="YES"),1,0)*((--(IF(NOT(TEXT(INDIRECT("COMPLETE!$L$2:$L$"&$O$2),"@")="NO"),1,0)))))))
- SORRY! THIS NEXT PART SHOULD BE AT THE LEFT MARGIN BECAUSE IT'S SEPARATE FROM C6 BUT I CAN'T SEEM TO EDIT THAT.
- C7 on Totals needs to count the rows on COMPLETE where F is "GLC Not Loaded".
- I have this working with this formula: SUMPRODUCT(--(IF(TEXT(INDIRECT("COMPLETE!$A$2:$A$"&$O$2),"MM/YYYY")=("0"&(MONTH(C$3&1))&"/"&$C$2),1,0))*(INDIRECT("COMPLETE!$F$2:$F$"&$O$2)="GLC NOT LOADED"))
- Second problem -
- Here's what C8 on Totals needs to count on COMPLETE:
- Any rows where F says "DOMESTIC/LANDED" or "WRONG SUPPLIER SITE".
- Here's what C9 on Totals needs to count on COMPLETE:
- Any rows that have not already been counted in F.
- Here's what C8 on Totals needs to count on COMPLETE:
That's all there is to it! (I do realize this is insane, but then, I work for crazy people, so there's that...)
I included the formulas that I currently have working so that, if you see that there's an easier way you can tell me and so that you can see what's there now in case that helps you to visualize this mess, LOL!
Can all of this even be done? The co-worker originally thought about a macro, but I got to thinking that having dynamic formulas on the Totals sheet would have the workbook be up to the minute at all times. If it would be easier with a macro, that's fine, too.
Thank you for even reading this novel!
Jenny
Last edited: