Is there a way to use wildcards for dates?

  • Thread starter Thread starter MildJoe
  • Start date Start date
M

MildJoe

I want to pick out all the dates in september 2008 from a column of many
dates, and use it in a SUMIF, but an asterix or questionmark wont work like
this:
=SUMIF(G7:G8;"??.09.2008";F7:F8)
=SUMIF(G7:G8;"*.09.2008";F7:F8)
 
Use SUMPRODUCT, wildcards can be avoided:

=SUMPRODUCT((MONTH(G7:G100)=9)*(F7:F100))

So if F7 thru G11 contains:

1 9/1/2008
2 10/1/2008
3 9/15/2008
4 6/6/2008
5 5/5/2008

the formula returns 4
 
Are you trying to sum the entries that occur in September of 2008?
=sumproduct(--(text(g7:g8,"yyyymm")="200809"),f7:f8)

Or sum the entries that occured on the 9th of any month in 2008:
=sumproduct(--(text(g7:g8,"yyyydd")="200809"),f7:f8)

If you wanted to just check the month:
=sumproduct(--(isnumber(g7:g8)),--(month(g7:g8)=1),f7:f8)

The extra =isnumber() check is to avoid counting empty cells as January.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Back
Top