Ask a new question

Using wildcards in Date formula

I have two workbooks named, SalesData and SalesAnalysis.  SalesData is downloaded monthly to an Excel file and contains two columns.  Column A has a range name of  InvDate and contains dates formatted as mm/dd/yyyy.  Column B is named Amount and contains dollars.  I wish to insert formulae in the second workbook, SalesAnalysis that will add the amounts for each month.  I want to use SalesAnalysis in subsequent years and I do not want to have to change the SalesAnalysis formula for the year each time the SalesData file is overwritten (updated).  I have tried the SUMIF and SUMIFS and it only works if I include the complete date, ie. “<=1/31/2012”.  I have tried using wildcards for the year but that does not work for me.  The formula I used in SalesAnalysis is: =SUMIF(SalesData.xlsx!InvDate,”<=1/31/2012”,SalesData.xlsx!Amount).  While this works for the year 2012, for 2013 I would have to change the formula to 1/31/2013.  Is there a way I can use wildcards for the year so I do not have to change the formula for the year?  Thanks for any help!
Answer
Answer

Hi,

 

Try this

 

1. Select the range of data (first row should be the header row) on SalesData workbook and press Ctrl+T.  Ensure that "My Data has headers box is checked" and click on OK

2. On the SalesAnalysis workbook, enter numbers from 1 to 12 in range A1:A12 of any worksheet

3. In cell B1 of this sheet, enter this formula and copy till cell B12

 

=SUMPRODUCT((MONTH(SalesData.xlsx!InvDate)=1)*(SalesData.xlsx!Amount))

 

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer
As far as I understood, you want to get result based on the criteria of day & Month irrespective of Year.

So use this formula
=SUMPRODUCT((MONTH(SalesData.xlsx!InvDate)<=1)*(SalesData.xlsx!Amount))

Here 1 represents the Jan,  if you want to get for May, then change it to 5 and like that.

If you remove the Less than ("<") sign then it will give result for that particular month only otherwise it will give cumulative result till that month.
Vijay

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 8, 2024 Views 11,481 Applies to: