Formula to Check multiple data entrys

Microsoft Excel 2003 (full product)
July 21, 2010 at 06:04:24
Specs: Windows XP
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 Found

I 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..


See More: Formula to Check multiple data entrys

Report •

#1
July 21, 2010 at 07:08:20
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


Report •

#2
July 21, 2010 at 08:46:16
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.

Report •

#3
July 21, 2010 at 09:08:27
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 you used the formula.

You say how many "Missing Elements" there are from the ones that meet that criteria.

Does this mean that you are not looking for the term "Missing Element" , but are looking for other words - if so you need to specify what is being counted.

Regards


Report •
Related Solutions


Ask Question