Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
I am trying to use a sumproduct function with IF and Text.
I want to see that if the value of the sumproduct function is "blank" or "specific text" then return another value, if not then return the value of the sumproduct function Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
=if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct())
-- Gary''s Student - gsnu200759 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
that's what I tried but unfortunately it always returns sumproduct(). Could
it be because the sumproduct() is not true "text", there is an underlying formula? Thanks for your help "Gary''s Student" wrote: =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
"mass" wrote in message
... that's what I tried but unfortunately it always returns sumproduct(). Could it be because the sumproduct() is not true "text", there is an underlying formula? Thanks for your help "Gary''s Student" wrote: =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 Try telling us what the sumproduct formula is, and an example of the data it works on. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
Can SUMPRODUCT() return Text?
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gary''s Student" wrote in message ... =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
for a bunch of calling destinations, i have two price lists. The first price
list is special rates which applies only to half the destinations. The second price list is standard that applies to all. Now i have a call list column which is a list of calls with call destinations that are in two columns (destination, type of call- eg fixed or mobile). I want to pull the rate for each call in the call list against the special price list. But if the special price list is blank (because there is no special rate- this is not the same as 0) then i want it to revert to the standard price list. Right now, i am using sumproduct to check both the destination country and type of call in the call list, against the same in the price list and it returns the standard price correctly. I can't seem to apply it with an IF() formula for the special pricing if the lookup pulls a blank. Thanks "Sandy Mann" wrote: Can SUMPRODUCT() return Text? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gary''s Student" wrote in message ... =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
the problem is that it is reading the NA or a blank in the special pricing
column and returning a zero. since the IF function relates to "" or "NA", it is not catching them. Unfortunately, "" or "NA" is not the same as 0 which is a special price in certain cases. there must be some way of getting it to return a text value? "Sandy Mann" wrote: I can't seem to apply it with an IF() formula for the special pricing if the lookup pulls a blank If you mean a real blank, (ie and empty cell), not an empty string, (ie "") then =IF(SUMPRODUCT(<Your data for special rate)=0,SUMPRODUCT(<Your data for standard rate),SUMPRODUCT(<Your data for special rate)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "mass" wrote in message ... for a bunch of calling destinations, i have two price lists. The first price list is special rates which applies only to half the destinations. The second price list is standard that applies to all. Now i have a call list column which is a list of calls with call destinations that are in two columns (destination, type of call- eg fixed or mobile). I want to pull the rate for each call in the call list against the special price list. But if the special price list is blank (because there is no special rate- this is not the same as 0) then i want it to revert to the standard price list. Right now, i am using sumproduct to check both the destination country and type of call in the call list, against the same in the price list and it returns the standard price correctly. I can't seem to apply it with an IF() formula for the special pricing if the lookup pulls a blank. Thanks "Sandy Mann" wrote: Can SUMPRODUCT() return Text? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gary''s Student" wrote in message ... =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct text if
As I said:
If you mean a real blank, (ie and empty cell), not an empty string, (ie "") Where are the "" and "NA" coming from? there must be some way of getting it to return a text value? SUMPRODUCT() as the *product* part suggests, works with numbers, not text. It can compare text but not return text. Do you want to send me a sample sheet so that we both know what it is that you are trying to do? If so then replace the part after the @ in my address below as it says. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "mass" wrote in message ... the problem is that it is reading the NA or a blank in the special pricing column and returning a zero. since the IF function relates to "" or "NA", it is not catching them. Unfortunately, "" or "NA" is not the same as 0 which is a special price in certain cases. there must be some way of getting it to return a text value? "Sandy Mann" wrote: I can't seem to apply it with an IF() formula for the special pricing if the lookup pulls a blank If you mean a real blank, (ie and empty cell), not an empty string, (ie "") then =IF(SUMPRODUCT(<Your data for special rate)=0,SUMPRODUCT(<Your data for standard rate),SUMPRODUCT(<Your data for special rate)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "mass" wrote in message ... for a bunch of calling destinations, i have two price lists. The first price list is special rates which applies only to half the destinations. The second price list is standard that applies to all. Now i have a call list column which is a list of calls with call destinations that are in two columns (destination, type of call- eg fixed or mobile). I want to pull the rate for each call in the call list against the special price list. But if the special price list is blank (because there is no special rate- this is not the same as 0) then i want it to revert to the standard price list. Right now, i am using sumproduct to check both the destination country and type of call in the call list, against the same in the price list and it returns the standard price correctly. I can't seem to apply it with an IF() formula for the special pricing if the lookup pulls a blank. Thanks "Sandy Mann" wrote: Can SUMPRODUCT() return Text? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gary''s Student" wrote in message ... =if(or((sumproduct()=""),(sumproduct()="text")),an othervalue,sumproduct()) -- Gary''s Student - gsnu200759 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
maybe by sumproduct or some other way with text. | Excel Worksheet Functions | |||
SUMPRODUCT but with text containing | Excel Worksheet Functions | |||
Is there a SUMPRODUCT-like function that I can use on text? | Excel Worksheet Functions | |||
Sumproduct Text | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions |