count function

  • Thread starter Thread starter Brenda
  • Start date Start date
B

Brenda

I've tried using this formula as a criterion

=SUM((PS2="#1 PM")*(NOTIFICATION="*"))

But I get the #NUM! error. I want to get a count of all
non-blank cells in the Notification column, if the PS2
column = "#1 PM".

TIA,
Brenda
 
=SUMPRODUCT((PS2="#1 PM")*(NOTIFICATION<>""))

Assumes Your ranges have been named PS2 and NOTIFICATION.
 
Ken:

I tried, to no avail. My ranges are named PS2 and
NOTIFICATION, I'm using CTRL+Shift+Enter, I'm not sure
what else to do.

Why SUMPRODUCT? Just curious...that is a new one to me.

Thank you sincerely,
Brenda
 
Hmmm - Works fine for me.

You don't need to array enter with SUMPRODUCT, but that wouldn't stop it working. Are you
able to mail me a sample sheet I can look at - You would need to desensitise any data, and
also take the NOSPAM out of my email address.
 
Copy one of the cells with #1 PM into an empty cell, type the criteria in
another cell
then use

=O2=P2

where O2 is the cell where you pasted the value and P2 the typed value
If you get false try

=LEN(O2)

and

=LEN(TRIM(O2))

if the numbers differs add trim to your formula

=sumproduct((trim(PS2)="etc..

if the same but different from

=LEN(P2)

then you probably have an invisible html character

=sumproduct((substitute(PS2,char(160),"")="etc
 
Hi Peo - It was the ranges. They were set up to be whole column references A:A and were
killing the arrays and Sumproduct.
 
Back
Top