Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09,
etc in column A and corresponding data in column B. Then I have a table on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like
to put in a SUMIF function where I can get totals for column B on sheet 1 by
month IF the date in Sheet 1 column A equals the month listed in the column A
in sheet 2. Right now, the only way I can do this is by creating a column C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet 1?

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would
like
to put in a SUMIF function where I can get totals for column B on sheet 1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet
1?

Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would
like
to put in a SUMIF function where I can get totals for column B on sheet 1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet
1?


.

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.

One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.



.

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I
can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.



.