EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

Nieve K Published at Dev

Nieve K

A screenshot of the spreadsheet1

Hi I am looking for a way (in the form of an Excel formula) that can do this

I need to count the number of "o" in columns with headings "Threat", for each row. For example, I need counts of "o"s in row 2, 4, 6, and so on, but only the "o"s in columns with heading "Threat" in row 1.

BUT it gets complicated, I need more. I need to distinguish the counts of "o"s, depending on if the cell beneath the "o" in columns "Threat" has the text "P" or "A".

So I need to find the way to count "o"s in row 2, only in columns with the heading "Threat", and only the "o"s with "P" in the cell underneath the "o".

Any advice will be greatly appreciated!

Edit: Come to think of it, since the "P"s and "A"s are in the same columns as "Threat", the heading can be taken out of the equation! So just need to find a formula that would help me count the number of a certain value in row 2, that are only in the column that has "P" in row 3.

teylyn

Try

=SUMPRODUCT(($A$2:$E$10="o")*($A$3:$E$11="P"))

Adjust the ranges to suit your sheet. Do not use with whole column references, unless you like staring at the "Calculating ..." message. If the spreadsheet grows, consider using dynamic ranges.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-26

Comments

0 comments

From Dev

Related Related

Article

EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

Remove text from excel columns with certain criteria

SQL - COUNT columns from separate tables based on a certain value

How can I count the number of integer occurences from a txt file from certain columns in Java?

finding value in certain columns only

How can I query multiple columns from a drop down menu and a count of a certain column

How to delete a certain value from columns in mysql

How can I exclude certain 'columns' from a list of tuples?

how to get certain columns from ps aux

how to get certain columns from dynamodb aws

How can I join datasets from multiple tables with only certain columns in EF Core?

How can I populate my listbox with only certain columns from a worksheet?

How can I split these columns from my table only in certain rows?

Omit certain columns from analyses

How can I get certain text from string in Lua?

Pandas Sum & Count Across Only Certain Columns

How to get a certain value from a text file

Count how many times certain text combinations occurs in certain columns

Only copy certain columns

read only certain columns

Return only certain columns

Selecting certain columns from a table with dates as columns

Count across columns if value is a certain character in R

How can I style only certain columns in a div?

How can i show only certain columns of data table in sql?

Can an Athena table with only certain columns be created?

How to get dynamodb to only return certain columns

How to take only certain columns from a FILTER result?

Copy only certain columns from one table to other and insert defa

Pandas: Creating new data frame from only certain columns