You are here:

Excel/Using CountIF & SumIF (Case-Sensitive)


Hi Sixthsense,

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.

        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


Hi Dean,

Thanks for sending your question on my way...

Make use of Exact Function with Sumproduct combination to get the desired result.  Exact function is a CASE SENSITIVITY function which compares the text with exact case of the text.



I have used the names inside the formula so that you can understand it easily.  You can replace the name mentioned in the formula to a cell reference too...

I just gone for sumproduct to avoid array.

If you want to go for array formula approach then try it in the below way...

Array Formula Requires CTRL+SHIFT+ENTER


...confirmed by pressing [B]CTRL+SHIFT+ENTER[/B] to activate the array, [U]not just ENTER[/U].  You will know the array is active when you see curly braces [B]{ }[/B] appear around your formula.  If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Press F2 on that cell and try again.


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




[Overview] Having hands-on experience with Excel, I can able to answer questions which is related to Conditional Formatting, Goal Seek, Scenarios, Subtotal, Validation, Pivot Table, Shared Workbook, Excel Sheet / Workbook Protection, Excel Settings, Excel Shortcuts and the Two Major & Massive Key Areas of Excel (i.e.) Worksheet Functions and Excel VBA (Macro).

[Services] Volunteer in many of the Excel Discussion Groups / Forums and answered more than 10,000+ questions (approx.)

[Client FAQs] My Solution Files

[Not Interested In] Chart Questions

[Something Controversial]
*50% of answers never been read by the questioner's.
*Only 5% of questioner's provide feedback about the suggested answers.


Vast Experience

Ms-Office, Oracle, Foxpro, C, COBOL etc.,

©2017 All rights reserved.