| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf formula
I need to create a function on this table that will sum the Cost + Price
based on the Category entered. For instance, each time Housing is selected in Category, I what to total the corresponding amounts in H (Cost) + I(Price). I tried =SUM(IF(D5:D1000,"=Housing",H5:I1000,0)) but it only gave me the answer 0. I also tried =SUMIF(D5:D1000,"Housing",H5:I1000) that also gives me a 0 answer. If I leave out the Cost in the formula, I get the right total. Its only when I include the Cost that I get the answer 0. Creating it as an array formula gives me 0 for an answer also. D E F H I J Category Type Vendor Cost Price Cost+Price D:F are Lookups H =IF(E5="TPD","$2.00","$0.00") J =IF(I60,J5+H6+I6, "") Does the formula in Cost restrict me from using the data in the Sum formula? If so how do I get around it? Thank you for your help. barb -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200811/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf formula
Hi,
Your data has wrapped in posting so it's difficult but I assume this is your cost formula =IF(E5="TPD","$2.00","$0.00") Putting the output in quites makes it Text so try this =IF(E5="TPD",$2.00,$0.00) Mike "BarbS via OfficeKB.com" wrote: I need to create a function on this table that will sum the Cost + Price based on the Category entered. For instance, each time Housing is selected in Category, I what to total the corresponding amounts in H (Cost) + I(Price). I tried =SUM(IF(D5:D1000,"=Housing",H5:I1000,0)) but it only gave me the answer 0. I also tried =SUMIF(D5:D1000,"Housing",H5:I1000) that also gives me a 0 answer. If I leave out the Cost in the formula, I get the right total. Its only when I include the Cost that I get the answer 0. Creating it as an array formula gives me 0 for an answer also. D E F H I J Category Type Vendor Cost Price Cost+Price D:F are Lookups H =IF(E5="TPD","$2.00","$0.00") J =IF(I60,J5+H6+I6, "") Does the formula in Cost restrict me from using the data in the Sum formula? If so how do I get around it? Thank you for your help. barb -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200811/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
| multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
| Sumif and And formula | Excel Discussion (Misc queries) | |||
| Formula using SUMIF & IF | Excel Worksheet Functions | |||
| Is there a MAXIF formula similar to the SUMIF formula? | Excel Discussion (Misc queries) | |||
Linear Mode
