|
Sumproduct with OR
Hi,
I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
try:
=SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
Hi Toppers,
Thanks for the answer. I'd like to probe (DataTable!$I$5:$I$65536={"MI","ILS"}) to include LAH 1 through to 100 but dont want to write into the array. Rather than type "LAH 1", "LAH2" etc etc i tried LAH* where * is 1 to 100 but this returned an error. How would i include this please? Thanks John "Toppers" wrote: try: =SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
Try:
=SUMPRODUCT(--(ISNUMBER(FIND({"LAH","MI","ISL"},I5:I65536))*(H5: H65536))) "John" wrote: Hi Toppers, Thanks for the answer. I'd like to probe (DataTable!$I$5:$I$65536={"MI","ILS"}) to include LAH 1 through to 100 but dont want to write into the array. Rather than type "LAH 1", "LAH2" etc etc i tried LAH* where * is 1 to 100 but this returned an error. How would i include this please? Thanks John "Toppers" wrote: try: =SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
Here's a version using the unary minuses as in the OP's original formula
=SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")0),DataTable!$U$5:$U$65536) -- Regards, Peo Sjoblom "Toppers" wrote in message ... try: =SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
Dyslexic fingers again! I meant of course double unary
-- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Peo, In this instance do you actually require the double unery or the 0 in --((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")0) surely as any cell can not hold exclusively "MI" or "ILS" at the same time then ((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")) would return 1 or 0 -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Here's a version using the unary minuses as in the OP's original formula =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")0),DataTable!$U$5:$U$65536) -- Regards, Peo Sjoblom "Toppers" wrote in message ... try: =SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
Sumproduct with OR
Could have been worse, at least you didn't type urinary <bg
You are of course right, it's not needed since they are in the same column, it's just me wanting to cover all unforeseen angles and different seasons -- Regards, Peo Sjoblom "Sandy Mann" wrote in message ... Dyslexic fingers again! I meant of course double unary -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Peo, In this instance do you actually require the double unery or the 0 in --((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")0) surely as any cell can not hold exclusively "MI" or "ILS" at the same time then ((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")) would return 1 or 0 -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Here's a version using the unary minuses as in the OP's original formula =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--((DataTable!$I$5:$I$65536="MI")+(DataTable!$I$5:$I $65536="ILS")0),DataTable!$U$5:$U$65536) -- Regards, Peo Sjoblom "Toppers" wrote in message ... try: =SUMPRODUCT((DataTable!$E$5:$E$65536=INDEX(Tbl_Mth s,MATCH($U$4,Tbl_Dropdown,0),2))*(DataTable!$F$5:$ F$65536="Base")*(DataTable!$H$5:$H$65536="SB SPU")*(DataTable!$I$5:$I$65536={"MI","ILS"})*(Data Table!$U$5:$U$65536)) "John" wrote: Hi, I searched through some of the questions here to find a solution to this, however, i'm more confused!! I would like to return a total where one of the columns i'm totalling has more than one criteria. Specifically in this formula i would like to total values for the criteria below, however for Col. I this should only be where the string is either "MI" or "ILS" The formula below returns #Value. =SUMPRODUCT(--(DataTable!$E$5:$E$65536=INDEX(Tbl_Mths,MATCH($U$4 ,Tbl_Dropdown,0),2)),--(DataTable!$F$5:$F$65536="Base"),--(DataTable!$H$5:$H$65536="SB SPU"),--OR(DataTable!$I$5:$I$65536="MI"),(DataTable!$I$5:$ I$65536="ILS"),(DataTable!$U$5:$U$65536)) I had written this formula where the total for MI was returned and added this to the ILS but i'd like to thinker what i'm trying to do is slicker! Any help on this would be great. Thanks John |
| All times are GMT +1. The time now is 05:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com