How to Resize Photos in Batch?: [Anwsered] Counting Records with multiple criteria

Friday, September 23, 2016

[Anwsered] Counting Records with multiple criteria

Counting Records with multiple criteria

I am trying to count the number of records in a membership list that meet two criteria as under and I keep
getting a #VALUE! Result.
The formula I am using is =SUM((D11:D10000="L")*(F11:F10000="NO"))
C D E F

30-06-2009

Membership List
Date
New

Member Name (Pref)
Category
Joined
Member

DOGGETT, MR ANTHONY P
L
01-Feb-10
YES

WARREN, MR JOHN W
L
20-Jul-65
NO

CHARNOCK, MR REG
SM
22-Jan-87
NO

HIGGINS, MR KENNETH C
SM
03-Aug-00
NO

VERCOE, MR NEVILLE
L
21-Feb-64
NO

HUGHES, MR MICHAEL JOHN
F
13-Mar-93
NO

JAMES, MR HERBERT E
SM
31-Dec-88
NO

Column D contains text and column F contains an IF formula which determines wether the joining date is
before or after 30-Jun-2009.
Also can I have the formula on another worksheet and use range names for the
column references.

Keys to the Problem Counting Records with multiple criteria

Download SmartPCFixer for Free Now

The formula =(D11:D10000="L") returns an array of True/False values.
You need to convert this to ones and zeroes, easiest way is to use =--(D11:D10000="L")
So the following array formula will work
=SUM(--(D11:D10000="L")*--(F11:F10000="NO"))
You can also use SUMPRODUCT to count values, no need for an Array formula.
=SUMPRODUCT(--(D11:D10000="L"),--(F11:F10000="NO"))

Ed Ferrero www.edferrero.com

Run CHKDSK to Fix Counting Records with multiple criteria

Go to the Start button.
In the Search box, type "cmd" (without quotes) and press Enter.
At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
Reboot your computer.

An error might pop up:

"Access denied as you do not have significant privileges. You have to invoke this utility in elevated mode."

If you receive the above error message, it means you are not logged into you computer as an Administrator. If that's the case, follow these steps to run the command prompt in elevated mode:

Go to theStart button.
Go to "All Programs > Accessories" and right click on "Command Prompt".
Select "Run as Administrator".
In the Search box, type "cmd" (without quotes) and press Enter.
At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
Reboot your computer.

Recommended Method to Fix the Problem: Counting Records with multiple criteria:

How to Fix Counting Records with multiple criteria with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.