ExcelBanter - Count and Sum if Date

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count and Sum if Date (https://www.excelbanter.com/excel-worksheet-functions/167564-count-sum-if-date.html)

Count and Sum if Date

I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450

I need a formula that sums all row 2 cells that have a November 2007 date in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina

Count and Sum if Date

One way

=SUMPRODUCT((MONTH(A1:Z1)=11)*(YEAR(A1:Z1)=2007)*( A2:Z3))

--

Regards,

Peo Sjoblom

"Tina Winter" wrote in message
...
I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450

I need a formula that sums all row 2 cells that have a November 2007 date
in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina

Count and Sum if Date

I have tried both suggestions and still get #VALUE as a result. Any
suggestions on what I am doing wrong?

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT((MONTH(A1:Z1)=11)*(YEAR(A1:Z1)=2007)*( A2:Z3))

--

Regards,

Peo Sjoblom

"Tina Winter" wrote in message
...
I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450

I need a formula that sums all row 2 cells that have a November 2007 date
in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina

Count and Sum if Date

Nevermind, I figured it out. My source data is from a pivot table and I was
selecting evaluation cells both inside and outside of the pivot table,
causing the error.

Thanks so much for your help and happy holidays!

"Tina Winter" wrote:

I have tried both suggestions and still get #VALUE as a result. Any
suggestions on what I am doing wrong?

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT((MONTH(A1:Z1)=11)*(YEAR(A1:Z1)=2007)*( A2:Z3))

--

Regards,

Peo Sjoblom

"Tina Winter" wrote in message
...
I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450

I need a formula that sums all row 2 cells that have a November 2007 date
in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina

Count and Sum if Date

Try this amendment to Peo's formula:

=SUMPRODUCT((MONTH(C1:Z1)=11)*(YEAR(C1:Z1)=2007)*( C2:Z2))

As he was starting in column A, you have some non-numerics in the
range. I've also made it Z2 at the end, as you had asked to add values
from row 2 only.

Hope this helps.

Pete

On Nov 27, 7:11 pm, Tina Winter
wrote:
I have tried both suggestions and still get #VALUE as a result. Any
suggestions on what I am doing wrong?

"Peo Sjoblom" wrote:
One way

=SUMPRODUCT((MONTH(A1:Z1)=11)*(YEAR(A1:Z1)=2007)*( A2:Z3))

--

Regards,

Peo Sjoblom

"Tina Winter" wrote in message
...
I realize this question has been answered before for other scenerios, but I
can't seem to get it to work for mine...Please Help!

Here is my table

A B C D E F
1 11/25/07 11/28/07 12/2/07 12/15/07
2 # 4.00 6.00 2.00 9.00
3 $ 200 300 100 450

I need a formula that sums all row 2 cells that have a November 2007 date
in
row 1.

Thank you, thank you, thank you to anyone who can help me.

Tina- Hide quoted text -

- Show quoted text -