You are here:

Excel/Using CountIF & SumIF to Find and Count Exact Match


Hi Gulshan,

Iím running MS Excel 2007 in an attempt to use multiple formulas using CountIf and SumIf (Case-Sensitive) to find matching Case-Sensitive NAME and the CountIF function to count the Sum  dollar Amount associated with the NAME.

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


Formula Screenshot
Formula Screenshot  
Hi Dean,

I was finding it quite difficult to build the whole thing into one formula, so I have broken down the requirement into different parts. This is assuming that you won't have too many cases, in above example, you have just 4 variations.
1. With row 1 used for headings, at the end of your data, add one column for each of the values and run Exact formula. Eg, assuming your above data is in columns A & B. Enter the 4 names in columns D1, E1, F1, G1. In cell D2, enter the formula =(EXACT($D$1,A2).In E2 to Ex, enter the formula =EXACT($E$1,A2) and so on. Drag the formula to the entire list.
2. Now, in another location, create the count and summations based on the number of Trues for each of the headings.

Hope this helps.

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




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 All rights reserved.