Is there a way to use wildcards for dates?
 

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)

Answer: Is there a way to use wildcards for dates?
 

Yes, you can use wildcards for dates in Excel. However, the syntax is a bit different than what you have tried.

To pick out all the dates in September 2008 from a column of many dates, you can use the following formula:

This formula uses

1. two SUMIF functions.
2. The first SUMIF function sums all the values in the range F7:F8 where the corresponding date in the range G7:G8 is greater than or equal to September 1st, 2008.
3. The second SUMIF function subtracts all the values in the range F7:F8 where the corresponding date in the range G7:G8 is greater than October 1st, 2008.
4. The result is the sum of all the values in the range F7:F8 where the corresponding date in the range G7:G8 is in September 2008.

Note that the dates in the range G7:G8 must be in a date format recognized by Excel. If they are not, you may need to convert them using the function or by changing the cell format to a date format.

Is there a way to use wildcards for dates?
 

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
--
Gary''s Student - gsnu200817


"MildJoe" wrote:

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)

Is there a way to use wildcards for dates?
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

MildJoe wrote:

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)

--

Dave Peterson