I'm using Excel 2007



In worksheet W1 I have a table of values with column names for the table in row 1 and row names in column A.
I have marked certain of the intersects in this table with an X
In a separate worksheet W2 I have a list of values ColumnName.RowName (not necessarily unique or in any particular order)

I have applied conditional formatting to my table so that cells that contain an X and for which there is no match on the list in W2 are highlighted

THis uses a formula applied to the data in the table similar to
=AND(B2="X",ISNA(MATCH($A2&"."&B$1,LookupList,0)))

(note the relative references)


This works fine. However I would also like to be able to count the highlighted values. I can't seem to find a way to do this.

Can anyone help

Thanks in advance

Hi TishyMouse,

To count the conditionally-coloured cells, use the same knid of formula you used for conditionally formatting them.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks, but how do I sum across a row of the table given that the true/false of the condition evaluation for each cell is based on a relative reference?
i.e. in cell B2 'TRUE' would be
=AND(B2="X",ISNA(MATCH($A2&"."&B$1,LookupList,0)))
whereas in cell B3 it would be
=AND(B3="X",ISNA(MATCH($A3&"."&B$1,LookupList,0)))

Without a workbook containing some representative data, specific advice can't be given. Can you attach such a workbook to a post (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sample file attached. I want to count the number of cells highlighted in red in the MessageDetails sheet. Thanks.

Attached Files

SumByCondition.xlsx (28.7 KB, 11 views)

On the MessageDetails sheet, try:
=SUMPRODUCT(($B$2:$F$7="X")*(ISNA(MATCH($B$1:$F$1& "."&$A2:$A$7,LookupList,0))))
On any other sheet, the equivalent is:
=SUMPRODUCT((MessageDetails!$B$2:$F$7="X")*(ISNA(M ATCH(MessageDetails!$B$1:$F$1&"."&MessageDetails!$ A$2:$A$7,LookupList,0))))

Note: For some reason, the board's sotware reformats long lines so that, instead of 'MATCH', for example, you may see 'M ATCH'. You'll need to correct any such breaks.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Fantastic, that works perfectly. Thanks!!!

If I could trouble you for another few minutes...

I have revised the sample sheet somewhat, see attached (you can see the exception count working correctly on the MessageDetails sheet). However now I want to also be able to count the values in the Data sheet highlighted in green (these are the combinations that do not have a corresponding 'X' in the MessageDetails matrix). Ideally without adding an additional column - the column 'ConditionCalculation' is there just to show how the conditional formatting is being applied. I have tried lots of combinations of SumProduct but I must be missing something. Can you steer me in the right direction?

Thanks again!

Attached Files

SumByConditionv3.xlsx (32.1 KB, 13 views)

Hi TishyMouse,

I think there's something wrong with your conditional format rules on the Data sheet.

It seems to me that the intent is to highlight those cells that don't have an 'X' match on the MessageDetails sheet, but that's not what's happening. However, if you correlate the highlights on the Data sheet with the values on the MessageDetails sheet, you'll see that the highlighting is applied to some cells for which there is an 'X' match on the MessageDetails sheet. Indeed, three of the four highlighted cells have an 'X' match on the MessageDetails sheet and one that doesn't have an 'X' match on the MessageDetails sheet isn't highlighted.

Assuming I'm correct about the above, the conditional format formula should be:
=OFFSET(MessageDetails!$A$1,MATCH($A2,MessageDetai lsSourceMessages,0),MATCH($B2,MessageDetailsColHea dings,0))=0
and the count of unmatched entries on the Data sheet should be 2, which you can get via:
=SUMPRODUCT((TableMessageDetails[[COL1]:[COL5]]=0)*NOT(ISNA(MATCH(TableMessageDetails[Source Message]&"."&TableMessageDetails[[#Headers],[COL1]:[COL5]],TableData[Row.Col],0))))

Note: As before, watch for unwanted spaces inserted by the board software.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks yes you are right about the existing error in the sheet (the perils of trying to replicate a problem for the purposes of illustration...)

Anyway happily this one is now sorted too. You are a mine of useful information! I can't pretend that I understand what is going on in the sumproduct formula though, although I managed to adopt it for the more complicated sheet I'm using it in. If you have time, I'd love to see an explanation for the formula - should reduce further pestering questions in the future ;-)

Thanks again

TM

Hi TishyMouse,

Surprising as it might seem, I just dabble in Excel ... my forte is Word.

For details of how SUMPRODUCT works, see: http://office.microsoft.com/en-us/ex...005209293.aspx

The internals of the formula are just a minor variant of the one you were already using for the exceptions which, of course, is based on the one in post#4.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

THanks I did already have a look at the Microsoft guide, but the bit I don't really get (and which isn't explained there) is your multiplication of functions applied to two arrays before sumproduct-ing them with the third array. What does the multiplication do in this instance?

i.e. sumproduct(fx(array1)*fx(array2),fx(array3))

And yes I do find it hard to believe that you only dabble in Excel. Can't imagine the sort of shenanigans you must achieve in Word ;-)

Basically, each array will return a series of true & false results that could be expressed as 1s (true) and 0s (false).
Suppose the first array returns:
1 0 1 0 1 0 1 0
and the second array returns:
0 0 0 1 1 0 1 1
If you multiply the corresponding values from the two array, to find which values are true in both, you end up with:
0 0 0 0 1 0 1 0
(i.e. two true pairs). The SUMPRODUCT simply sums the 1s.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]