Counting Cells across Multiple Columns

[longbow007]

Hello, could someone please help me with counting in Excel 2003 for the following:

Please find my attached spreadsheet (no sensitive data).

Workbook Name: Counting Cells

Worksheet Name: Master_List

I am trying to count the number of cells in columns W, X, Y, Z & AA that have a numeric value greater than zero in each of the columns and also match the text value of “AOG” in column G.

In the attached spreadsheet, if I filter column G for text value of “AOG” and count the number of cells in columns: W,X,Y,Z & AA that have a numeric value of greater than zero, then I should get a numeric result of: 24.

You may notice that in some of these columns, there is no numeric value greater than zero. That is because the information in columns: W,X,Y,Z & AA change daily.

If someone could please help, it would be greatly appreciated.

Kind regards,

Chris

[AlKey]

enter in AB3 and pull it across

Please Login or Register  to view this content.

Just noticed that you need it for Excel 2003

Use this array formula

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[longbow007]

Hi AlKey, thank you for your reply - much appreciated. Unfortunately, I am using Excel 2003 and COUNTIFS is not a function in Excel 2003.

Is there a way to achieve what I need using Excel 2003 please?

Kind regards,

Chris

[AlKey]

Have you tried the array formula?

[longbow007]

Hi AlKey, thank you for the array formula. It works fine. Really appreciate your help

[Phuocam]

Or try this:
=SUMPRODUCT((G:G="AOG")*((W:W>0)+(X:X>0)+(Y:Y>0)+(Z:Z>0)+(AA:AA>0)))

[longbow007]

Thank you Phuocam for your excellent code - very much appreciated.

Kind regards,

Chris

[Phuocam]

You're welcome

[Tony Valko]

=SUMPRODUCT((G:G="AOG")*((W:W>0)+(X:X>0)+(Y:Y>0)+(Z:Z>0)+(AA:AA>0)))

You should avoid using entire columns as range references in the SUMPRODUCT function.

It will evaluate every cell referenced.

That single formula is evaluating 6,291,456 cells!

[Phuocam]

You should avoid using entire columns as range references in the SUMPRODUCT function.

It will evaluate every cell referenced.

That single formula is evaluating 6,291,456 cells!

Yes, here is a sample, thank you!