Excel 2007: Matching Year & Month without regard to Day

[David Lipetz]

Folks,

I regularly use a SUMPRODUCT formula to derive totals from a large array of
transactional data. If I am looking to calculate the sales total for a
specific month, I generally use the following formula:

=SUMPRODUCT(--(YEAR(A1)=YEAR(InvDate),--(MONTH(A1)=MONTH(InvDate),(Total))

where:
A1 references the date that I want to evaluate
InvDate = named range of transactional data - date column
Total = named range of transactional data - invoice total column


If just use DATE rather than YEAR and MONTH, Excel only totals transactions
for the single day. If I just want the month and year without regard to day,
is there a more efficient way of doing it than the above?

Thanks,
David

 

[Pete_UK]

Try it this way:

=SUMPRODUCT(--(TEXT(A1,"yymm")=TEXT(InvDate,"yymm")),Total)

assuming InvDate and Total are named ranges of equal size and A1
contains a date whose month and year you are interested in.

The format strings could equally well be "mmyy" or even "mmyyyy".

Hope this helps.

Pete

 

[Ron Rosenfeld]

Folks,

I regularly use a SUMPRODUCT formula to derive totals from a large array of
transactional data. If I am looking to calculate the sales total for a
specific month, I generally use the following formula:

=SUMPRODUCT(--(YEAR(A1)=YEAR(InvDate),--(MONTH(A1)=MONTH(InvDate),(Total))

where:
A1 references the date that I want to evaluate
InvDate = named range of transactional data - date column
Total = named range of transactional data - invoice total column


If just use DATE rather than YEAR and MONTH, Excel only totals transactions
for the single day. If I just want the month and year without regard to day,
is there a more efficient way of doing it than the above?

Thanks,
David

I don't know whether it is more efficient, but (in Excel 2007, as you wrote),
you could use the SUMIFS function.

To obtain the transaction total of the same month/year as is in A1:

=SUMIFS(Total,InvDate,">="&A1,InvDate,"<="&EOMONTH(A1,0))


--ron