if true, all are true

I have a list of data where if one record is true, all records are true. Is there a formula or function to make that work in Excel 2013? I'd prefer not to use macros, if possible.

Example: column "A" has date. Column "B" has category (options = 1,2,3...18). Column "C" is TRUE/FALSE based on value in category, default = FALSE. Grouped by date, if any record is category 18, then all records for the same date must be TRUE in column C. There are many records for each date. Captured data points do not follow a structured path, so category values fall randomly in the list, meaning lookups/matching/indexes don't work (that I have figured out). Category 18 may or may not show up on any given date. For clarity, the T/F question attached to each record is, "does any record for this date fall into category 18?"

In the past I've used pivots to list only dates with the pertinent category, with a vlookup in the T/F field (lookup_value = date and table_array = the pivot). It works, though it's clunky.

How is the C TRUE/FALSE determined?

As stated, "does any record for this date fall into category 18?" (T/F). Maybe I can clarify. Let's say record "1" for Jan 13, 2015 is category 5 and record "2" is category 18. In this case I want to flag both records 1 and 2 as TRUE, because record 2 is true (it is in fact category 18). Does that help?

Good afternoon,

Try this:

=IF(SUMPRODUCT(($A$1:$A$1000=A1)*($B$1:$B$1000=18))>0,"TRUE","FALSE")

Array functions aren't exactly my forte, but hopefully this works.

Best of luck,

Modified it to fit the real data and it works just fine. Eats up the processor for a minute or two, but returns correct results. Thanks!

Maybe a bit quicker processing.... if you have Excel 2007 or later...

=COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,18)>0