I am trying to create a summary page for a very complex spreadsheet. My main goal is to break it down by country then have the summary page display how many items there are based on a specific text string.

So i have the country in one column a status of the item in another and the text string which i need to know how many are in the whole column in the last column.

I would need it to note say that its Brazil, its Unresolved and its a missing element. then tell me how many missing elements there are in total.

Example:

A B C

BRAZIL UNRESOLVED Missing Element

BRAZIL UNRESOLVED Missing Element

BRAZIL UNRESOLVED Element Found

BRAZIL UNRESOLVED Missing Element

USA RESOLVED Missing Element

USA RESOLVED Missing Element

BRAZIL UNRESOLVED Element FoundI would need it to find all BRAZIL's that are Unresolved and then tell me the total that are Missing Elements as a number.

Hope this explains it..

Hi, Using your example data in cells A2 to C8

This formula returns a count of the number of rows containing BRAZIL, UNRESOLVED & Missing element

=SUMPRODUCT(($A$2:$A$8="BRAZIL")*($B$2:$B$8="UNRESOLVED")*($C$2:$C$8="Missing Element"))Change the ranges to match.

The name "BRAZIL" can be replaced by a cell reference, either a single cell with a country name drop down list, or extend the formula down alongside a list of country names, so if D4 contains BRAZIL put this in E4:

=SUMPRODUCT(($A$2:$A$8=D4)*($B$2:$B$8="UNRESOLVED")*($C$2:$C$8="Missing Element"))

put USA in D5 and drag the formula down a row and it becomes this

=SUMPRODUCT(($A$2:$A$8=D5)*($B$2:$B$8="UNRESOLVED")*($C$2:$C$8="Missing Element"))Hope this helps

Regards

Thanks for the reply.

I think a better explanation will help.

I need excel to check the "Country" column for the Country I specify then check the "Status" column that it is "Unresolved" then tell me how many "Missing Elements" there are from the ones that meet that criteria. in the form of a number.

Hi, The formula returns the number of "Missing Element" entries for rows that have the specified country and the value "UNRESOLVED"

Isn't that what you asked for.

Using your example the formula I proposed returned the number 3.

What happened when

youused the formula.You say

how many "Missing Elements" there are from the ones that meet that criteria.Does this mean that you are

notlooking for the term "Missing Element" , but are looking for other words - if so you need to specify what is being counted.Regards

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History