#1   Report Post  
Old November 16th 08, 08:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 2
Default 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(D51000,"=Housing",H5:I1000,0)) but it only gave me the
answer “0”. I also tried =SUMIF(D51000,"Housing",H5:I1000) that also gives
me a “0” answer. If I leave out the “Cost” in the formula, I get the right
total. It’s 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   Report Post  
Old November 16th 08, 08:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default 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(D51000,"=Housing",H5:I1000,0)) but it only gave me the
answer “0”. I also tried =SUMIF(D51000,"Housing",H5:I1000) that also gives
me a “0” answer. If I leave out the “Cost” in the formula, I get the right
total. It’s 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sumproduct formula within a sumif formula. jerrymcm Excel Discussion (Misc queries) 2 October 3rd 07 03:35 PM
multiply formula where 1 cell has a (sumif) formula as a result kcip Excel Worksheet Functions 1 May 3rd 07 07:41 AM
Sumif and And formula StephenAccountant Excel Discussion (Misc queries) 5 November 9th 06 05:11 AM
Formula using SUMIF & IF BiggyTwo Excel Worksheet Functions 3 March 18th 06 03:36 AM
Is there a MAXIF formula similar to the SUMIF formula? tlc Excel Discussion (Misc queries) 2 March 13th 06 09:07 PM


All times are GMT +1. The time now is 06:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017