ExcelBanter - Count based on criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count based on criteria (https://www.excelbanter.com/excel-worksheet-functions/243833-count-based-criteria.html)

Max2073

Count based on criteria

I have a spreadsheet with 8000 rows.

In column "I" I have text. eg. "Emily", "Greg" and "John"
In Column "D" I have numbers.

I need to count the rows whe

Column I is equal to Emily and Column D is < 30
Column I is equal to Greg and Column D is < 50
Column I is equal to John and Column D is < 70

Gary''s Student

Count based on criteria

=SUMPRODUCT((D1:D8000="Emily")*(E1:E8000<30))
=SUMPRODUCT((D1:D8000="Greg")*(E1:E8000<50))
=SUMPRODUCT((D1:D8000="John")*(E1:E8000<70))

--
Gary''s Student - gsnu200904

Gary''s Student

Count based on criteria

Correction:

=SUMPRODUCT((I1:I8000="Emily")*(D1:D8000<30))
=SUMPRODUCT((I1:I8000="Greg")*(D1:D8000<50))
=SUMPRODUCT((I1:I8000="John")*(D1:D8000<70))

--
Gary''s Student - gsnu200904

"Max2073" wrote:

I have a spreadsheet with 8000 rows.

In column "I" I have text. eg. "Emily", "Greg" and "John"
In Column "D" I have numbers.

I need to count the rows whe

Column I is equal to Emily and Column D is < 30
Column I is equal to Greg and Column D is < 50
Column I is equal to John and Column D is < 70

All times are GMT +1. The time now is 05:49 AM.