Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Tim Tim is offline
external usenet poster
 
Posts: 408
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Tim Tim is offline
external usenet poster
 
Posts: 408
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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
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
how to sumif in excel with a not equal criteria Ed New Users to Excel 2 April 30th 23 07:45 PM
is there any way to have multiple TEXT criteria in a 'sumif' funct datasorter Excel Worksheet Functions 2 July 28th 06 07:16 PM
Can I Rank on a second criteria if the first is equal? CohenRB Excel Discussion (Misc queries) 3 June 23rd 06 01:54 PM
SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text James T Excel Discussion (Misc queries) 4 May 25th 06 08:00 PM
sumif if cell does not equal #N/A Micah Excel Discussion (Misc queries) 1 February 2nd 06 10:41 PM


All times are GMT +1. The time now is 01:55 PM.

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

About Us

"It's about Microsoft Excel"