SUMIF QUESTION

[Lady Layla]

I have a spreadsheet where I want to try to automate it more than what it is
currently



Col B contains a dates of invoices in cells B8 through B118
Col E contains Invoice amounts

B122 contains 12/31/03
B123 contains 01/31/04
B124 contains 02/29/04
on down to 12/31/04

In E123 I want to basically calculate the total of all Jan dated invoices by
coming up with a formula that says if the date in B8:B118 is greater than B122
but less than or equal to B123, sum E8:E118. Then I will copy this down E so
that each row beneath will give me total of Feb, total of Mar etc.


=sumif(AND(B8:b118>E123,B8:B118 <=E124),E8:E118)

This is not working. What can I do to correct this? Am I using the correct
formula?

Thank you so very much for the assistance!

 

[Don Guillett]

use sumproduct
=sumproduct((daterng>d123)*(daterng<d124)*sumrng)

 

[Domenic]

I have a spreadsheet where I want to try to automate it more than what it is
currently



Col B contains a dates of invoices in cells B8 through B118
Col E contains Invoice amounts

B122 contains 12/31/03
B123 contains 01/31/04
B124 contains 02/29/04
on down to 12/31/04

In E123 I want to basically calculate the total of all Jan dated invoices by
coming up with a formula that says if the date in B8:B118 is greater than B122
but less than or equal to B123, sum E8:E118. Then I will copy this down E so
that each row beneath will give me total of Feb, total of Mar etc.


=sumif(AND(B8:b118>E123,B8:B118 <=E124),E8:E118)

This is not working. What can I do to correct this? Am I using the correct
formula?

Thank you so very much for the assistance!

One way,

1) Since your only concern with dates is month and year, you can enter your
dates in this format: January 2004, February 2004, etc...

2) Put this formula in E122 and copy down,

=SUM(IF((MONTH($B$8:$B$118)=MONTH(B122))*(YEAR($B$8:$B$118=YEAR(B122))),$E$8
:$E$118)), to be entered using Ctrl + Shift + Enter

Hope this helps!

 

[Peo Sjoblom]

Try

=SUMIF($B$8:$B$118,">"&B122,$E$8:$E$118)-SUMIF($B$8:$B$118,">"&B123,$E$8:$E$
118)

you are contradicting yourself a bit with your formula using E123 and E124
but at the same time saying B122
and B123 etc. I assumed that E123 was a typo and that the first/last dates
are in B122 going down, if not replace B122
with E122 and so on.

 

[Lady Layla]

Thank you to Don, Domenic & Peo.

I tried Don's and got a Value Error, but tried this fomula and it does exactly
what I need!

Thank you so very very very much!


: Try
:
: =SUMIF($B$8:$B$118,">"&B122,$E$8:$E$118)-SUMIF($B$8:$B$118,">"&B123,$E$8:$E$
: 118)
:
: you are contradicting yourself a bit with your formula using E123 and E124
: but at the same time saying B122
: and B123 etc. I assumed that E123 was a typo and that the first/last dates
: are in B122 going down, if not replace B122
: with E122 and so on.
:
: --
:
: Regards,
:
: Peo Sjoblom
:
:
: : > I have a spreadsheet where I want to try to automate it more than what it
: is
: > currently
: >
: >
: >
: > Col B contains a dates of invoices in cells B8 through B118
: > Col E contains Invoice amounts
: >
: > B122 contains 12/31/03
: > B123 contains 01/31/04
: > B124 contains 02/29/04
: > on down to 12/31/04
: >
: > In E123 I want to basically calculate the total of all Jan dated invoices
: by
: > coming up with a formula that says if the date in B8:B118 is greater than
: B122
: > but less than or equal to B123, sum E8:E118. Then I will copy this down E
: so
: > that each row beneath will give me total of Feb, total of Mar etc.
: >
: >
: > =sumif(AND(B8:b118>E123,B8:B118 <=E124),E8:E118)
: >
: > This is not working. What can I do to correct this? Am I using the
: correct
: > formula?
: >
: > Thank you so very much for the assistance!
: >
: >
:
: