You are here:

Excel/MS Excel - Help Using CountIF and SumIF


Hi Nathan,

Hope all is well.  I can really use your help with a MS Excel problem, Iím trying to figure out, but cannot.

Iím running MS Excel 2007 in an attempt to use multiple formulas using CountIf and SumIf (Case-Sensitive) to find matching NAME must be Case-Sensitive and Count the Sum of the dollar Amount.

I have a spreadsheet that contains the person's Name and Amount paid information; the issue that I'm having and would like to resolve is that I have multiple Names with very similar spelling but different letter cases, including I have the amount paid.

I would like to be able to have Excel find each Name (case-sensitive) and add the total matching dollar amount paid.  I've enclosed a sample below, please email me directly at .

        Col. A    Col. B
Row 1:   Name      Amount  (Field Name)
Row 2:   jSmith     $252
Row 3:   jsmith     $425
Row 4:   JSmith     $652
Row 5:   jSmith     $252
Row 6:   jSmith     $252
Row 7:   jsmith     $425
Row 8:   JSMITH     $655

The output in written format is what I would like to achieve in MS Excel using a function Formulas.

jSmith  $756  (Name { jSmith } Count = 3, Amount {$252} = $756
jsmith  $850  (Name { jsmith } Count = 2, Amount {$425} = $850
JSmith  $652  (Name { JSmith } Count = 1, Amount {$652} = $652
JSMITH  $655  (Name { JSMITH } Count = 1, Amount {$655} = $655

Email Address:



You will not be able to use COUNTIF or SUMIF because those functions are not case sensitive (as you already figured out).  The following formula is an array function that will calculate the dollar amount.  Please note array functions must be confirmed with CTRL-SHIFT-ENTER instead of just ENTER:


Here is a count:


I have uploaded a sample spreadsheet here with all of the formulas including your text labels:

Please remember to donate for the time I spent answering your question!!!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




This expert requires a donation of $5 for questions answered (satisfaction guaranteed). Isn't $5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).


I have been using spreadsheets since Lotus 1-2-3 was released.

Certified Public Accountant (CPA)

©2016 All rights reserved.