RE: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed
Would be good if you provide a sample file Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Matty Sent: 26 September 2012 8:07 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed I need to sum up a range of data based on multiple criteria using only the first number in a date string. For example I have a range of data I have to sum attached to specific dates. I need to sum the total product for the month and I am trying to base that criteria on the first number in the date line. The LEFT function came to mind but that seems to be for text only and returns an error value. I don't want to have to use a different formula for each date range. I need something that is going to recognize that first number in the range and sum accordingly as the number of dates is inconsistent. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed
Here it is. I hope this helps. Using Sheet 2 for the source data to display on Sheet 1. I believe I got as far as I could with the function but also believe it will show you what I am trying to do. I tried using the LEFT function but can't figure out a way to make it work. I am trying to sum the data for individual days into a month. Thanks! On Wednesday, September 26, 2012 11:19:24 AM UTC-4, Rajan_Verma wrote: *Would be good if you provide a sample file * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *Matty *Sent:* 26 September 2012 8:07 *To:* excel-...@googlegroups.com javascript: *Subject:* $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed I need to sum up a range of data based on multiple criteria using only the first number in a date string. For example I have a range of data I have to sum attached to specific dates. I need to sum the total product for the month and I am trying to base that criteria on the first number in the date line. The LEFT function came to mind but that seems to be for text only and returns an error value. I don't want to have to use a different formula for each date range. I need something that is going to recognize that first number in the range and sum accordingly as the number of dates is inconsistent. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Sample File.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed
Hi Use this See the attached =SUMPRODUCT(N(MONTH(Sheet1!$A$3:$A$122)=MONTH(Sheet2!$A61))*OFFSET(INDEX(Sh eet1!$B$2:$R$121,,MATCH(1,N((LEFT(Sheet1!$B$2:$Q$2,LEN(Sheet2!B$4))*1)=B$4), 0)),1,,,SUM(N((LEFT(Sheet1!$B$2:$Q$2,LEN(Sheet2!B$4))*1)=B$4 Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Matty Sent: 26 September 2012 9:07 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed Here it is. I hope this helps. Using Sheet 2 for the source data to display on Sheet 1. I believe I got as far as I could with the function but also believe it will show you what I am trying to do. I tried using the LEFT function but can't figure out a way to make it work. I am trying to sum the data for individual days into a month. Thanks! On Wednesday, September 26, 2012 11:19:24 AM UTC-4, Rajan_Verma wrote: Would be good if you provide a sample file Regards Rajan verma +91 7838100659 [IM-Gtalk] From: excel-...@googlegroups.com javascript: [mailto:excel-...@googlegroups.com javascript: ] On Behalf Of Matty Sent: 26 September 2012 8:07 To: excel-...@googlegroups.com javascript: Subject: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed I need to sum up a range of data based on multiple criteria using only the first number in a date string. For example I have a range of data I have to sum attached to specific dates. I need to sum the total product for the month and I am trying to base that criteria on the first number in the date line. The LEFT function came to mind but that seems to be for text only and returns an error value. I don't want to have to use a different formula for each date range. I need something that is going to recognize that first number in the range and sum accordingly as the number of dates is inconsistent. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com javascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript: . -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing
Re: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed
Genius! Thank you so much! On Wednesday, September 26, 2012 12:31:04 PM UTC-4, Rajan_Verma wrote: *Hi* *Use this* *See the attached* * * * =SUMPRODUCT(N(MONTH(Sheet1!$A$3:$A$122)=MONTH(Sheet2!$A61))*OFFSET(INDEX(Sheet1!$B$2:$R$121,,MATCH(1,N((LEFT(Sheet1!$B$2:$Q$2,LEN(Sheet2!B$4))*1)=B$4),0)),1,,,SUM(N((LEFT(Sheet1!$B$2:$Q$2,LEN(Sheet2!B$4))*1)=B$4 * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *Matty *Sent:* 26 September 2012 9:07 *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed Here it is. I hope this helps. Using Sheet 2 for the source data to display on Sheet 1. I believe I got as far as I could with the function but also believe it will show you what I am trying to do. I tried using the LEFT function but can't figure out a way to make it work. I am trying to sum the data for individual days into a month. Thanks! On Wednesday, September 26, 2012 11:19:24 AM UTC-4, Rajan_Verma wrote: *Would be good if you provide a sample file * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *Matty *Sent:* 26 September 2012 8:07 *To:* excel-...@googlegroups.com *Subject:* $$Excel-Macros$$ Sumif Based on First Number of a Date Range Help Needed I need to sum up a range of data based on multiple criteria using only the first number in a date string. For example I have a range of data I have to sum attached to specific dates. I need to sum the total product for the month and I am trying to base that criteria on the first number in the date line. The LEFT function came to mind but that seems to be for text only and returns an error value. I don't want to have to use a different formula for each date range. I need something that is going to recognize that first number in the range and sum accordingly as the number of dates is inconsistent. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread
