Excel Downloads

Excel Online Consulting

Tips on Excel

 

Tips on VBA for Excel

Menu Bar (Click on the buttons below to rediscover Excel Spreadsheet Tips)

COUNTIF is an obsolete function inherited from LOTUS-123

What can be done with COUNTIF
Can be done more easily with SUMPRODUCT
What can't be done with COUNTIF
Can be done with SUMPRODUCT

Counting with Excel COUNTIF

With the COUNTIF function, you can count the number of different products based on a single criteria like date OR city OR salesperson... SUMPRODUCT is like Excel COUNTIF on steroids, with SUMPRODUCT you can count the number of different products based on date AND city AND salesperson AND any number of other criteria...

When you use Excel SUMPRODUCT function formulas in a report to look at a set of data, you can just change the data and the report is AUTOMATICALLY refreshed.

The Excel COUNTIF Function

The general format for the COUNTIF formula is as follow:
=COUNTIF(range where criteria applies, criteria, count what)
Applied to the following table, the result of the formula

=COUNTIF(C2:C7,"=John",D2:D7)
the result should be "3":

 

A

B

C

D

1

Month

State

Name

Amount

2

Ma

MI

John

2

3

Ju

NY

Peter

6

4

Ma

PA

John

8

5

De

NH

Peter

3

6

No

RI

John

4

7

Oc

FL

Peter

6

The Excel COUNTIF function is limited to one criteria (one column). If you want to count the amounts based on month, State and name, discover the very simple SUMPRODUCT function with which you can use 2, 3 or more criteria with "or",  "and"...SUMPRODUCT is like COUNTIF on steroids.

Menu Bar (Click on the buttons below to rediscover Excel Spreadsheet Tips)

Comments: Click Here

VBA for Excel macros

Excel VBA Online Consulting

Tips on Excel

 

Tips on VBA for Excel