SUMIF & COUNTIF based on dates

D

Danny

Hi,

I got formulas' 1 & 2 from this NG and tried to make come up with the
formula I needed from samples posted in this NG but I was not able to.

Please help.

Columns: G=Amount, I=Date Posted and J=Date Sent
1. =SUMIF($I$2:$I1116,"=6/30/07",$G$2:$G1116) = Sum up items in Col: G
based on Col: I dated 6/30/07
2. =COUNTIF($I$2:$I1116,"=6/30/2007") = Count items in Col: G based on Col:
I dated 6/30/07

Need formulas to include Col: J as additional creiteria:
a. Change "=6/30/07" to inclusive dates, i.e., 1/1/07 to 6/30/07
b. Sum up items in Col: G based on Col: I dated 1/1/07 to 6/30/07 and Col: J
(dated)
c. Sum up items in Col: G based on Col: I dated 1/1/07 to 6/30/07 and Col: J
NOT sent (cell is blank)
d. Formulas to COUNT a, b, and c
 
T

Toppers

Not sure if a is what you want:

Try:

a.
=Sumproduct(--($I$2:$I1116>=date(07/01/01)),--($I$2:$I1116<=date(07/06/30)),$G$2:$G1116)

b.
=Sumproduct(--($I$2:$I1116>=date(07/01/01)),--($I$2:$I1116<=date(07/06/30)),--($J$2:$J1116<>""),$G$2:$G1116)

c.
=Sumproduct(--($I$2:$I1116>=date(07/01/01)),--($I$2:$I1116<=date(07/06/30)),--($J$2:$J1116=""),$G$2:$G1116)

HTH
 
D

Danny

Hi,

I tried the formula and a message comes up "You entered too few arguments
for this function ............"
 
T

Toppers

.... sorry about DATE format ... should be as "Teethless Mama" responded.
(it's been a long day!)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(07/06/30)),$G$2:$G1116)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(2007,06,30)),--($J$2:$J1116<>""),$G$2:$G1116)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(2007,06,30)),--($J$2:$J1116=""),$G$2:$G1116)
 
D

Danny

Perfect! Thanks a lot!

Toppers said:
... sorry about DATE format ... should be as "Teethless Mama" responded.
(it's been a long day!)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(07/06/30)),$G$2:$G1116)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(2007,06,30)),--($J$2:$J1116<>""),$G$2:$G1116)

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(2007,06,30)),--($J$2:$J1116=""),$G$2:$G1116)
 
T

Toppers

=Sumproduct(--($I$2:$I1116>=date(2007,01,01)),--($I$2:$I1116<=date(07/06/30))

is count between the two dates
 
D

Danny

Hi,

I tried the count formula and a message comes up "You entered too few
arguments
Also, will count the Col: I based on the Col: J criteria.

I was also requesting the formula to COUNT the results of formulas B & C
that you provided.

Thanks again.
 
T

Toppers

..... I had the DATE error again ( I just copied a previous posting without
checking!).

Anyway, glad it's all working.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top