Help with Function
Ok I need help. I have a spreadsheet with two worksheets. It's a general
ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
Help with Function
=COUNTIF(column with number=5000)-countif(column with number=6000)
"Helpmeeee" wrote: Ok I need help. I have a spreadsheet with two worksheets. It's a general ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
Help with Function
Where do I put it in the following function?
=SUMIF('Itemized Revenue'!$L:$L,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Revenue'!$D:$D)+SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) 5000 is in cell A6, (it was actually 3 pages, Itemized rev, itemized exp, and totals) Do I just replace A6? "Sean Timmons" wrote: =COUNTIF(column with number=5000)-countif(column with number=6000) "Helpmeeee" wrote: Ok I need help. I have a spreadsheet with two worksheets. It's a general ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
Help with Function
=SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=5000),--('Itemized
Revenue'!$L$2:$L$50000<6000),('Itemized Revenue'!$D:$D)) will return all values from itemized revenue with a value of 5000 - 5999. Now, if you want to refer to the 5000 range, try: =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D)) Same exact thing for expenses =SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) so, to add them together, =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D))+SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) "Helpmeeee" wrote: Where do I put it in the following function? =SUMIF('Itemized Revenue'!$L:$L,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Revenue'!$D:$D)+SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) 5000 is in cell A6, (it was actually 3 pages, Itemized rev, itemized exp, and totals) Do I just replace A6? "Sean Timmons" wrote: =COUNTIF(column with number=5000)-countif(column with number=6000) "Helpmeeee" wrote: Ok I need help. I have a spreadsheet with two worksheets. It's a general ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
Help with Function
Thanks, the spreadsheet right now splits the totals up by month too, that is
in C4. How would you include that? "Sean Timmons" wrote: =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=5000),--('Itemized Revenue'!$L$2:$L$50000<6000),('Itemized Revenue'!$D:$D)) will return all values from itemized revenue with a value of 5000 - 5999. Now, if you want to refer to the 5000 range, try: =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D)) Same exact thing for expenses =SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) so, to add them together, =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D))+SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) "Helpmeeee" wrote: Where do I put it in the following function? =SUMIF('Itemized Revenue'!$L:$L,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Revenue'!$D:$D)+SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) 5000 is in cell A6, (it was actually 3 pages, Itemized rev, itemized exp, and totals) Do I just replace A6? "Sean Timmons" wrote: =COUNTIF(column with number=5000)-countif(column with number=6000) "Helpmeeee" wrote: Ok I need help. I have a spreadsheet with two worksheets. It's a general ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
Help with Function
=SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized
Revenue'!$L$2:$L$50000<A6+1000),--(MONTH('Itemized Revenue'!$A$2:$A$50000)=MONTH(C4)),('Itemized Revenue'!$D:$D))+SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),,--(MONTH('Itemized Expenses'!$A$2:$A$50000)=MONTH(C4)),('Itemized Expenses'!$E$2:$E$50000)) Assuming the dates are in collumn A of your Revenue and Expenses worksheets. Change A to whatever column they are actually in. "Helpmeeee" wrote: Thanks, the spreadsheet right now splits the totals up by month too, that is in C4. How would you include that? "Sean Timmons" wrote: =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=5000),--('Itemized Revenue'!$L$2:$L$50000<6000),('Itemized Revenue'!$D:$D)) will return all values from itemized revenue with a value of 5000 - 5999. Now, if you want to refer to the 5000 range, try: =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D)) Same exact thing for expenses =SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) so, to add them together, =SUMPRODUCT(--('Itemized Revenue'!$L$2:$L$50000=A6),--('Itemized Revenue'!$L$2:$L$50000<A6+1000),('Itemized Revenue'!$D:$D))+SUMPRODUCT(--('Itemized Expenses'!$J$2:$J$50000=A6),--('Itemized Expenses'!$J$2:$J$50000<A6+1000),('Itemized Expenses'!$E$2:$E$50000)) "Helpmeeee" wrote: Where do I put it in the following function? =SUMIF('Itemized Revenue'!$L:$L,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Revenue'!$D:$D)+SUMIF('Itemized Expenses'!$J:$J,"="&($A6&TEXT(C$4,"mmm-yy")),'Itemized Expenses'!$E:$E) 5000 is in cell A6, (it was actually 3 pages, Itemized rev, itemized exp, and totals) Do I just replace A6? "Sean Timmons" wrote: =COUNTIF(column with number=5000)-countif(column with number=6000) "Helpmeeee" wrote: Ok I need help. I have a spreadsheet with two worksheets. It's a general ledger and I have the number 5000 set up for advertising. When I go to itemize the advertising I want to use say 5100 for Pay Per Click, 5200 flyers, ect. I have one worksheet that adds the total of my expenses (advertising, supplies, ect), and one has them itemized (Flyers, Pay Per Click, Paper, Ect.) What I need is a formula that will pick up a range of numbers, ei 5000 through 5999, in the itemized worksheet and include them in my total for Advertising (5000) on my worksheet with the totals. The itemized colum will have numbers ranging from 1000-15999(or more) that will represent all my expenses from advertising to ultilties. Any help? I need something like {=if(itemized expense ranges from 5000-5999)then include it in the total for adversting (5000)} I just don't know how to look and include a range of numbers. |
All times are GMT +1. The time now is 05:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com