Formula to count month-specific items
 

Hello,

I need a formula to do the following.

Column A = ascending dates, e.g. 01/01/07, 02/01/07 etc
Column B = a figure corresponding to its date in column A

I then have different cells corresponding to the 12 months. I need each of
these to show the total figure from column B but only those from its specific
month. So, I could do with an example of a formula to use. Let's say the
total for January will be in cell C2. What formula would I put in that cell
to make a total for column B, but only for items from January (the dates
being in column A)?

This is a clarification of an earlier post which may have been too confusing
as there have been no replies.

Many thanks!

Formula to count month-specific items
 

On Thu, 4 Jan 2007 04:51:00 -0800, IoHeFy
wrote:

Hello,

I need a formula to do the following.

Column A = ascending dates, e.g. 01/01/07, 02/01/07 etc
Column B = a figure corresponding to its date in column A

I then have different cells corresponding to the 12 months. I need each of
these to show the total figure from column B but only those from its specific
month. So, I could do with an example of a formula to use. Let's say the
total for January will be in cell C2. What formula would I put in that cell
to make a total for column B, but only for items from January (the dates
being in column A)?

This is a clarification of an earlier post which may have been too confusing
as there have been no replies.

Many thanks!

If you want to sum the values for January 2007, you can use this:

=SUMIF(A:A,"="&DATE(2007,1,1),B:B) - SUMIF(A:A,""&DATE(2007,1,31),B:B)

Obviously, you can substitute a cell reference for the DATE function; or for
part of it.

For example, if you had 1 January 2007 in C1, you could substitute:

=SUMIF(A:A,"="&C1,B:B) - SUMIF(A:A,""& C1+32-DAY(C1+32),B:B)




--ron

Formula to count month-specific items
 

=SUMPRODUCT((MONTH($A$1:$A$100)=1)*SUMIF($A$1:$A$1 00,$A$1:$A$100&"",$B$1:$B$100))

this assumes you're looking at 100 rows of data, and allows for blank rows.
if you want to sum February, change the month()=1 to =2...

"IoHeFy" wrote:

Hello,

I need a formula to do the following.

Column A = ascending dates, e.g. 01/01/07, 02/01/07 etc
Column B = a figure corresponding to its date in column A

I then have different cells corresponding to the 12 months. I need each of
these to show the total figure from column B but only those from its specific
month. So, I could do with an example of a formula to use. Let's say the
total for January will be in cell C2. What formula would I put in that cell
to make a total for column B, but only for items from January (the dates
being in column A)?

This is a clarification of an earlier post which may have been too confusing
as there have been no replies.

Many thanks!

Formula to count month-specific items
 

Check your earlier post, subject: Formula Madness.........you have two replys
there

Vaya con Dios,
Chuck, CABGx3

"IoHeFy" wrote:

Hello,

I need a formula to do the following.

Column A = ascending dates, e.g. 01/01/07, 02/01/07 etc
Column B = a figure corresponding to its date in column A

I then have different cells corresponding to the 12 months. I need each of
these to show the total figure from column B but only those from its specific
month. So, I could do with an example of a formula to use. Let's say the
total for January will be in cell C2. What formula would I put in that cell
to make a total for column B, but only for items from January (the dates
being in column A)?

This is a clarification of an earlier post which may have been too confusing
as there have been no replies.

Many thanks!

Formula to count month-specific items
 

Try filling down in columns C and D:

C D
Jan-07 =SUMIF(A:A,"<"&C2,B:B)-SUMIF(A:A,"<"&C1,B:B)
Feb-07 =SUMIF(A:A,"<"&C3,B:B)-SUMIF(A:A,"<"&C2,B:B)

IoHeFy wrote:
Hello,

I need a formula to do the following.

Column A = ascending dates, e.g. 01/01/07, 02/01/07 etc
Column B = a figure corresponding to its date in column A

I then have different cells corresponding to the 12 months. I need each of
these to show the total figure from column B but only those from its specific
month. So, I could do with an example of a formula to use. Let's say the
total for January will be in cell C2. What formula would I put in that cell
to make a total for column B, but only for items from January (the dates
being in column A)?

This is a clarification of an earlier post which may have been too confusing
as there have been no replies.

Many thanks!