I received some help yesterday to write an expression to count only results that were in the previous month,
Today I'm wondering if I can combine the previous code with the code below using an IF/And nested statement.
Please Login or Register to view this content.
Would it look something like this?Please Login or Register to view this content.
I can post a link for the worksheet if you need, but just looking to combine those two criteria into one.Please Login or Register to view this content.
Last edited by noobtoexcel; 03-01-2012 at 02:50 PM.
Are you trying to count last months only if F <3 ?
then use
=SUMPRODUCT(--(TEXT(E2:E16,"mm-yy")=TEXT(EOMONTH(TODAY(),-1),"mm-yy")),--(F2:F16<3))
Yes, looks like you have too many (or misplaced) parentheses though. Also, what is $F:$F<3 supposed to mean? Try
=IF(AND(SUMPRODUCT(--(TEXT($E:$E,"mm-yy")=TEXT(EOMONTH(TODAY(),-1),"mm-yy")),COUNT($F:$F<3)),1,0)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That accepted the expression, but is giving me a #VALUE! error now. To give you some background on what I'm trying to accomplish. I'm trying to calculate turnaround times based on two different date fields. I am calculating the turnaround times in a query in access. Then I'm exporting to excel to calculate a few more things. I'm using this code to count only the records that we have received during the previous month.
Then I'm using this code to give me a value to calculate percentages of how many have met our turnaround time of <3 days.Please Login or Register to view this content.
It gives me a 1 if that turnaround time is met, and a 0 if it isn't. Then I would like to compare the ones that met the turnaround time against the count of the records for the previous month. This is why I'm looking to calculate with the two codes together so that I have one field that has both calculations, and one that only has the total count of records received.Please Login or Register to view this content.
Originally Posted by ChemistB
From what you say, carsto's formula should work for you. Expand the range from 16 to whatever number you need. Carsto didn't use E:E or F:F because large ranges like that in SumProduct will really slow down your workbook. Best to keep the range as small as possible. Does that work for you?
I modified Carsto's coding to this and it is now working. Thanks so much!
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks