Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif , 2 criteria, text must equal
Hi there,
I need to sum the 'amt' column when the 'code' is equal to 2115 and the 'description' column contains 'gst refund'. Thanks for anyone's help. code type description amt 2115 AR-PY 4TH QTR GST REFUND 2,100.00 2115 GL-2 FEB'09 GST DEDUCTED 325.32 2115 GL-2 FEB'09 CASH EXPENSES 5.00 2115 GL-2 4TH GST REFUND quarter 5.66 2115 GL-2 4TH QTR GST-TRAVEL 6.50 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif , 2 criteria, text must equal
From your sample, it's hard to tell where one column ends and the next
beigns: AR-PY 4TH QTR GST REFUND So, I'm assuming AR-PY is in one column and 4TH QTR GST REFUND is in another. =SUMPRODUCT(--(A2:A6=2115),--(ISNUMBER(SEARCH("GST Refund",C2:C6))),D2:D6) Or, using cells to hold the criteria: F2 = 2115 G2 = GST Refund =SUMPRODUCT(--(A2:A6=F2),--(ISNUMBER(SEARCH(G2,C2:C6))),D2:D6) In Excel 2007: =SUMIFS(D2:D6,A2:A6,2115,C2:C6,"*GST Refund*") =SUMIFS(D2:D6,A2:A6,F2,C2:C6,"*"&G2&"*") -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi there, I need to sum the 'amt' column when the 'code' is equal to 2115 and the 'description' column contains 'gst refund'. Thanks for anyone's help. code type description amt 2115 AR-PY 4TH QTR GST REFUND 2,100.00 2115 GL-2 FEB'09 GST DEDUCTED 325.32 2115 GL-2 FEB'09 CASH EXPENSES 5.00 2115 GL-2 4TH GST REFUND quarter 5.66 2115 GL-2 4TH QTR GST-TRAVEL 6.50 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif , 2 criteria, text must equal
Thank you...it works great.
"T. Valko" wrote: From your sample, it's hard to tell where one column ends and the next beigns: AR-PY 4TH QTR GST REFUND So, I'm assuming AR-PY is in one column and 4TH QTR GST REFUND is in another. =SUMPRODUCT(--(A2:A6=2115),--(ISNUMBER(SEARCH("GST Refund",C2:C6))),D2:D6) Or, using cells to hold the criteria: F2 = 2115 G2 = GST Refund =SUMPRODUCT(--(A2:A6=F2),--(ISNUMBER(SEARCH(G2,C2:C6))),D2:D6) In Excel 2007: =SUMIFS(D2:D6,A2:A6,2115,C2:C6,"*GST Refund*") =SUMIFS(D2:D6,A2:A6,F2,C2:C6,"*"&G2&"*") -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi there, I need to sum the 'amt' column when the 'code' is equal to 2115 and the 'description' column contains 'gst refund'. Thanks for anyone's help. code type description amt 2115 AR-PY 4TH QTR GST REFUND 2,100.00 2115 GL-2 FEB'09 GST DEDUCTED 325.32 2115 GL-2 FEB'09 CASH EXPENSES 5.00 2115 GL-2 4TH GST REFUND quarter 5.66 2115 GL-2 4TH QTR GST-TRAVEL 6.50 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif , 2 criteria, text must equal
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tim" wrote in message ... Thank you...it works great. "T. Valko" wrote: From your sample, it's hard to tell where one column ends and the next beigns: AR-PY 4TH QTR GST REFUND So, I'm assuming AR-PY is in one column and 4TH QTR GST REFUND is in another. =SUMPRODUCT(--(A2:A6=2115),--(ISNUMBER(SEARCH("GST Refund",C2:C6))),D2:D6) Or, using cells to hold the criteria: F2 = 2115 G2 = GST Refund =SUMPRODUCT(--(A2:A6=F2),--(ISNUMBER(SEARCH(G2,C2:C6))),D2:D6) In Excel 2007: =SUMIFS(D2:D6,A2:A6,2115,C2:C6,"*GST Refund*") =SUMIFS(D2:D6,A2:A6,F2,C2:C6,"*"&G2&"*") -- Biff Microsoft Excel MVP "Tim" wrote in message ... Hi there, I need to sum the 'amt' column when the 'code' is equal to 2115 and the 'description' column contains 'gst refund'. Thanks for anyone's help. code type description amt 2115 AR-PY 4TH QTR GST REFUND 2,100.00 2115 GL-2 FEB'09 GST DEDUCTED 325.32 2115 GL-2 FEB'09 CASH EXPENSES 5.00 2115 GL-2 4TH GST REFUND quarter 5.66 2115 GL-2 4TH QTR GST-TRAVEL 6.50 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sumif in excel with a not equal criteria | New Users to Excel | |||
is there any way to have multiple TEXT criteria in a 'sumif' funct | Excel Worksheet Functions | |||
Can I Rank on a second criteria if the first is equal? | Excel Discussion (Misc queries) | |||
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text | Excel Discussion (Misc queries) | |||
sumif if cell does not equal #N/A | Excel Discussion (Misc queries) |