| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
# of days - if & count statements
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
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 "Karen Smith" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
=COUNTIF(A1:A7,"<=30") =SUMPRODUCT(--(A1:A7<=30),B1:B7) HTH "Karen Smith" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
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 wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
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 wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
How would i say greater than 31 days less than 60 days?
"stevebriz" wrote: 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 wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
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 -- Brevity is the soul of wit. "Karen Smith" wrote: How would i say greater than 31 days less than 60 days? "stevebriz" wrote: 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 wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
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$400031<61),pseries!$T$8:$T$4000) Can anyone help? "Karen Smith" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400031),--(pseries!$S$8:$S$4000<61),pseries!$T$8:$T$4000) "Karen Smith" wrote: 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$400031<61),pseries!$T$8:$T$4000) Can anyone help? "Karen Smith" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
..... should be ...
=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400030),--(pseries!$S$8:$S$4000<=60),pseries!$T$8:$T$4000) "Toppers" wrote: =SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400031),--(pseries!$S$8:$S$4000<61),pseries!$T$8:$T$4000) "Karen Smith" wrote: 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$400031<61),pseries!$T$8:$T$4000) Can anyone help? "Karen Smith" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of days - if & count statements
=SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400030),--(pseries!$S$8:$S$4000<=60),pseries!$T$8:$T$4000 =SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400060),--(pseries!$S$8:$S$4000<=90),pseries!$T$8:$T$4000 etc "Toppers" wrote: =SUMPRODUCT(--(pseries!$I$8:$I$4000="advertising"),--(pseries!$S$8:$S$400031),--(pseries!$S$8:$S$4000<61),pseries!$T$8:$T$4000) "Karen Smith" wrote: 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$400031<61),pseries!$T$8:$T$4000) Can anyone help? "Karen Smith" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need help with formula | Excel Discussion (Misc queries) | |||
| Formula to count number of days in range which are less than today | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| count if function with if statements | Excel Worksheet Functions | |||
Linear Mode
