Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > SUMIF & COUNTIF based on dates
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default SUMIF & COUNTIF based on dates
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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,718
Default SUMIF & COUNTIF based on dates
date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default SUMIF & COUNTIF based on dates
.... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
How about the COUNTIF formulas?

Sorry, I was too excited when the formulas worked!

"Danny" wrote:

Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default SUMIF & COUNTIF based on dates
=Sumproduct(--($I$2:$I1116=date(2007,01,01)),--($I$2:$I1116<=date(07/06/30))

is count between the two dates

"Danny" wrote:

How about the COUNTIF formulas?

Sorry, I was too excited when the formulas worked!

"Danny" wrote:

Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
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.

"Toppers" wrote:

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

is count between the two dates

"Danny" wrote:

How about the COUNTIF formulas?

Sorry, I was too excited when the formulas worked!

"Danny" wrote:

Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 148
Default SUMIF & COUNTIF based on dates
I figured it out! I deleted $G$2:$G1116.

Thank you!!!

"Danny" wrote:

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.

"Toppers" wrote:

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

is count between the two dates

"Danny" wrote:

How about the COUNTIF formulas?

Sorry, I was too excited when the formulas worked!

"Danny" wrote:

Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default SUMIF & COUNTIF based on dates
..... I had the DATE error again ( I just copied a previous posting without
checking!).

Anyway, glad it's all working.

"Danny" wrote:

I figured it out! I deleted $G$2:$G1116.

Thank you!!!

"Danny" wrote:

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.

"Toppers" wrote:

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

is count between the two dates

"Danny" wrote:

How about the COUNTIF formulas?

Sorry, I was too excited when the formulas worked!

"Danny" wrote:

Perfect! Thanks a lot!

"Toppers" wrote:

... 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)


"Danny" wrote:

Hi,

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

"Teethless mama" wrote:

date(07/01/01) ??? Won't work

should be: DATE(2007,01,01)


"Toppers" wrote:

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

"Danny" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I want to use sumif to sum up a column based on a range of dates Ricky from Pine Island Excel Worksheet Functions 2 April 13th 07 03:04 PM
SUMIF/COUNTIF Tatebana Excel Discussion (Misc queries) 3 February 16th 07 01:50 PM
Need help on countif and sumif function with dates and wildcard characters chinita_jill Excel Discussion (Misc queries) 5 July 19th 06 05:22 PM
Countif/Sumif Cain Excel Worksheet Functions 2 February 12th 06 07:59 PM
Countif/Sumif Cain Excel Worksheet Functions 0 February 12th 06 07:35 PM


All times are GMT +1. The time now is 03:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement