Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
Hi,
I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
Hi
Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
You just had to complicate things didnt you? :)
Of course I have to take the year in account as well <sigh, I overlooked that detail. Your formula works, thanks for it. What does '--' do in a formula? I tried to expand the formula with more matches but getting errors again. This formula is such a pain to work with. I am using your formula as : =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<"")*(-- (YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which works fine. But I also want to include another fruit to the equation. I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it doesnt work. Get zero as a result. Tried with commas variation and get errors either Value# or NA#. So what is the trick here for the Sumproduct formula to add "Apples" and "Oranges" matching "todays Month"? Thanks for your help. On Jan 1, 5:00*am, RichardSchollar wrote: Hi Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
-- converts a boolean true or false to 1 or 0, which you'd need if the terms
in SUMPRODUCT are separated by commas, but the multiplication will probably do the job without the double unary minus. Is it a surprise to you that you get a zero if your formula includes ....*(--(TRIM(BH3:BH18)="Apple")))*(--(TRIM(BH3:BH18)="Orange")))) ? You have asked a row where BH = "Apple" AND BH = "Orange". It can't be equal to both. You need an OR condition, not AND. -- David Biddulph wrote in message ... You just had to complicate things didnt you? :) Of course I have to take the year in account as well <sigh, I overlooked that detail. Your formula works, thanks for it. What does '--' do in a formula? I tried to expand the formula with more matches but getting errors again. This formula is such a pain to work with. I am using your formula as : =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<"")*(-- (YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which works fine. But I also want to include another fruit to the equation. I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it doesnt work. Get zero as a result. Tried with commas variation and get errors either Value# or NA#. So what is the trick here for the Sumproduct formula to add "Apples" and "Oranges" matching "todays Month"? Thanks for your help. On Jan 1, 5:00 am, RichardSchollar wrote: Hi Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
Also maybe you can help me with this one.
I read about setting a dynamic range name to automatically expand the list. I thought that would be a good solutin to my expanding list. I named it CallDate and used the formula : =OFFSET(Tracker!$BD $3,0,0,COUNT(Tracker!$BD:$BD),1) BD being the column that has the dates that I want to verify against todays month (and year). =CountA(Calldate) does give me the right number of rows which means that the Name is working, but replacing the ranges in the Sumproduct with CallDate I get a #REF error. Also tried using Indirect(CallDate) as well and get another error. This formula makes me want to punch something... brb. :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
=SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY())),--(YEAR(BD3:BD18)=YEAR(TODAY())),--(BD3:BD18<""),SIGN((TRIM(BH3:BH18)="Apple")+(TRIM (BH3:BH18)="Orange")))
You don't need * and --, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... You just had to complicate things didnt you? :) Of course I have to take the year in account as well <sigh, I overlooked that detail. Your formula works, thanks for it. What does '--' do in a formula? I tried to expand the formula with more matches but getting errors again. This formula is such a pain to work with. I am using your formula as : =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<"")*(-- (YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which works fine. But I also want to include another fruit to the equation. I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it doesnt work. Get zero as a result. Tried with commas variation and get errors either Value# or NA#. So what is the trick here for the Sumproduct formula to add "Apples" and "Oranges" matching "todays Month"? Thanks for your help. On Jan 1, 5:00 am, RichardSchollar wrote: Hi Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
You can't replace all ranges with a dynamic range, you have to have a
dynamic range for all different columns tested, but based upon the same COUNT. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Also maybe you can help me with this one. I read about setting a dynamic range name to automatically expand the list. I thought that would be a good solutin to my expanding list. I named it CallDate and used the formula : =OFFSET(Tracker!$BD $3,0,0,COUNT(Tracker!$BD:$BD),1) BD being the column that has the dates that I want to verify against todays month (and year). =CountA(Calldate) does give me the right number of rows which means that the Name is working, but replacing the ranges in the Sumproduct with CallDate I get a #REF error. Also tried using Indirect(CallDate) as well and get another error. This formula makes me want to punch something... brb. :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
As a matter of interest, do you need the SIGN function, Bob?
I could see the need for that if the terms that you are trying to use in an OR function might both be true, but as in this case they are mutually exclusive I would have thought one could live without it? -- David Biddulph "Bob Phillips" wrote in message ... =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY())),--(YEAR(BD3:BD18)=YEAR(TODAY())),--(BD3:BD18<""),SIGN((TRIM(BH3:BH18)="Apple")+(TRIM (BH3:BH18)="Orange"))) You don't need * and --, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... You just had to complicate things didnt you? :) Of course I have to take the year in account as well <sigh, I overlooked that detail. Your formula works, thanks for it. What does '--' do in a formula? I tried to expand the formula with more matches but getting errors again. This formula is such a pain to work with. I am using your formula as : =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<"")*(-- (YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which works fine. But I also want to include another fruit to the equation. I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it doesnt work. Get zero as a result. Tried with commas variation and get errors either Value# or NA#. So what is the trick here for the Sumproduct formula to add "Apples" and "Oranges" matching "todays Month"? Thanks for your help. On Jan 1, 5:00 am, RichardSchollar wrote: Hi Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
I think you are right David, it is overkill in this instance.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... As a matter of interest, do you need the SIGN function, Bob? I could see the need for that if the terms that you are trying to use in an OR function might both be true, but as in this case they are mutually exclusive I would have thought one could live without it? -- David Biddulph "Bob Phillips" wrote in message ... =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY())),--(YEAR(BD3:BD18)=YEAR(TODAY())),--(BD3:BD18<""),SIGN((TRIM(BH3:BH18)="Apple")+(TRIM (BH3:BH18)="Orange"))) You don't need * and --, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... You just had to complicate things didnt you? :) Of course I have to take the year in account as well <sigh, I overlooked that detail. Your formula works, thanks for it. What does '--' do in a formula? I tried to expand the formula with more matches but getting errors again. This formula is such a pain to work with. I am using your formula as : =SUMPRODUCT(--(MONTH(BD3:BD18)=MONTH(TODAY()))*--(BD3:BD18<"")*(-- (YEAR(BD3:BD18)=YEAR(TODAY()))*(--(TRIM(BH3:BH18)="Apple")))) which works fine. But I also want to include another fruit to the equation. I tried adding *(--(TRIM(BH3:BH18)="Orange")))) to the end and it doesnt work. Get zero as a result. Tried with commas variation and get errors either Value# or NA#. So what is the trick here for the Sumproduct formula to add "Apples" and "Oranges" matching "todays Month"? Thanks for your help. On Jan 1, 5:00 am, RichardSchollar wrote: Hi Try: =SUMPRODUCT(--(MONTH(A1:A18=MONTH(TODAY())),--(A1:A18<""),--(B1:B18)) The --(A1:A18<"") term is inserted as blank cells return a 1 inside the MONTH function - which may or may not have caused you problems. The above does assume that your dates don't span 2 or more years, as then you may end up adding up values from the same month but different years. Richard On Jan 1, 8:24 am, " wrote: Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using sumproduct in a dynamic range?
Create the dynamic ranges. You need 2 of them (but could do it another way
using 1). This assumes your range in column BD will be contiguous (no empty/blank cells *within* the range). InsertNameDefine Name: Dates Refers to: =OFFSET(BD$3,,,COUNTA(BD$3:BD$65536)) Name: Fruit Refers to: =OFFSET(BH$3,,,COUNTA(BD$3:BD$65536)) Notice how *both* ranges are "keyed" on column BD using COUNTA(BD$3:BD$65536) Then your formula: =SUMPRODUCT((TEXT(Dates,"m/yyyy")=TEXT(NOW(),"m/yyyy"))*(TRIM(Fruit)={"apple","orange"})) -- Biff Microsoft Excel MVP wrote in message ... Hi, I am trying to use Sumproduct to give me the sum of a range but I cant get it to work Row A contains dates Row B contains Boolean (true or false) What I want to do is to sum all the Row B that are true and that row A date is equal to current month date. I've been playing with : =SUMPRODUCT(A1:A18")=MONTH(TODAY())*(B1:B18=True) but it gives me an error. Tried different approaches and cant get it to work. Can someone fix this for me please? The second part of my question is how to adapt the formula to an increase range. Every day the row count increases by one, can the formula automatically use all the rows that have dates instead of a fixed range A1:A18? If it is simpler to use VB it would be okay as well, but you will have to tell me how to call the function from within the spreadsheet? Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help please-SUMPRODUCT and Dynamic Range | Excel Worksheet Functions | |||
Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) |