|
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 |
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 |
| All times are GMT +1. The time now is 09:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com