# of days - if & count statements

[Karen Smith]

I need to create 2 formulas on the following table ....1 how many records are
0-30 days (answer 4) and 2ndly how much money are in 0-30 days (answer $750)

For example
# of Days $
28 150
34 100
22 100
44 100
1 200
32 50
5 300

 

[Ron Coderre]

Try this:

With your table in A1:B10

Count of records 0-30 days:
=COUNTIF(A2:A10,"<=30")

Sum of Amounts for records 0-30 days:
=SUMIF(A2:A10,"<=30",B2:B10)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Toppers]

=COUNTIF(A1:A7,"<=30")

=SUMPRODUCT(--(A1:A7<=30),B1:B7)

HTH

 

[stevebriz]

for the count of days <31

If the Range of days is A1 : A10

=COUNTIF(A1:A10,"<31")


For the sum of money <31 days amounts in col B1:B10

= SUMIF(A1:A10,"<31",B1:b10)

 

[stevebriz]

for the count of days <31

If the Range of days is A1 : A10

=COUNTIF(A1:A10,"<31")


For the sum of money <31 days amounts in col B1:B10

= SUMIF(A1:A10,"<31",B1:b10)

 

[Karen Smith]

How would i say greater than 31 days less than 60 days?

 

[Dave F]

One way would be =COUNTIF(A1:A10,"<60")-COUNTIF(A1:A10,">30") where you're
just subtracting the count of days greater than 30 from the count of days
less than 60.

Dave

 

[Karen Smith]

Here's the formula that I have now. I need to make formulas for >30 <=60
and so on down the road every 30 days.

=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000<=30),pseries!$T$8:$T$4000)

I've tried the one below but it doesn't seem to give me the answer
=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000>31<61),pseries!$T$8:$T$4000)

Can anyone help?

 

[Toppers]

=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000>31),--(pseries!$S$8:$S$4000<61),pseries!$T$8:$T$4000)

 

[Toppers]

..... should be ...


=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000>30),--(pseries!$S$8:$S$4000<=60),pseries!$T$8:$T$4000)

 

[Toppers]

=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000>30),--(pseries!$S$8:$S$4000<=60),pseries!$T$8:$T$4000

=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$4000>60),--(pseries!$S$8:$S$4000<=90),pseries!$T$8:$T$4000

etc