Sumif that does two things

[south0085]

I'm using Excel 2010.

I have some line at the top of my spreadsheet that show the item number (purchases), the item description, the quantity we need, taxable or not taxable, the cost of the item, and lastly the total.

At the bottom I have a line for freight (manually entered) and a line for tax.

My sumif formula says that if you see any lines above that are taxable ("T"), total them and multiply by 6%.

But I need to add to that same formula, this: If any of those line are in fact taxable, then ALSO look at the freight line and calculate that tax and add it here in this cell.

But if there are no taxable lines above, then don't calculate tax on the freight.

Basically what I'm saying is. I don't ONLY want to calculate tax on the lines above. But I also want to add the tax of the freight (if there are any taxable items above).

Here's what my sheet looks like. I can send this to anyone via attachment if you'd like:

Thank you so much for your help!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
3	*	*	*	*	*	*	*	*	*	*
4	Date	*			Supplier No.	S500017				*
5	Name	*			Supplier	A & A MACHANICAL SERVICE INC				*
6	Signature	*			Address	LOUISVILLE, KY	*	*	*	*
7	Dept	4100	ACCOUNTING/IT		Requested Delivery Date	*				*
8	CCR No.	*			*	*				*
9	Request	*					*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	PURPOSE	*					*	*	*	*
12	*	*					*	*	Order Currency	JPY
13	*	*	*	*	*	*	*	*	*	*
14	AccountCode	AccountGroup	ClassCode	Item No.	Description	Quantity	UnitMeasure	TaxNo Tax	UnitPrice	Subtotal
15	80060	COGS Travel Expense	4100	4511	*	1	kg	T	100.00	100.00
16	80060	COGS Travel Expense	4100	4511	*	1	kg	NT	200.00	200.00
17	80060	COGS Travel Expense	4100	4511	*	1	kg	T	300.00	300.00
18	80060	COGS Travel Expense	4100	4511	*	1	kg	NT	400.00	400.00
19	80060	COGS Travel Expense	4100	4511	*	1	kg	NT	500.00	500.00
20	80060	COGS Travel Expense	4100	4511	*	1	kg	T	600.00	600.00
21	80060	COGS Travel Expense	4100	4511	*	1	kg	NT	700.00	700.00
22	-	-	-	*	*	*	*	NT	*	0.00
23	-	-	-	*	*	*	*	NT	*	0.00
24	-	-	-	*	*	*	*	NT	*	0.00
25	*	*	*	*	*	TOTAL before Tax & Freight	*	*	JPY	2,800.00
26	$ * * * * *1,000.00	*	*	*	*	Freight Cost (Taxable if Item is)	*	*	JPY	20.00
27	*	*	*	*	*	Tax	6%	T	JPY	60.00
28	* Quotation(s) or Invoice(s) must be attached to be approved.	*	*	*	*	GRAND TOTAL	*	*	JPY	2,880.00
29	*	*	*	*	*	Estimated Cost in USD	*	120.48	USD	23.90
Purchase Requisition Form

 

[AlphaFrog]

Try something like this. Freight_Cost is pretax

=SUMIF($H$15:$H$24,"T",$J$15:$J$24) + (Freight_Cost * (1 + IF(COUNTIF($H$15:$H$24,"T"),6%, 0)))

 

[south0085]

Try something like this. Freight_Cost is pretax

=SUMIF($H$15:$H$24,"T",$J$15:$J$24) + (Freight_Cost * (1 + IF(COUNTIF($H$15:$H$24,"T"),6%, 0)))

Thank you, Alphafrog!

I modified it slightly to this:

'=SUMIF($H$15:$H$24,"T",$J$15:$J$24)*6%+(J26*(IF(COUNTIF($H$15:$H$24,"T"),6%,0)))

I didn't need the "1+" since I only want to add the tax, not the freight AND the tax.

But I needed the jump start. Thanks for the formula idea. Big help.

 

[AlphaFrog]

You're welcome. Thanks for the feedback and glad you worked it out.

Alternatively:
'=(SUMIF($H$15:$H$24,"T",$J$15:$J$24)+IF(COUNTIF($H$15:$H$24,"T"),J26,0))*6%