This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Dear experts

I need a formulae in excel which will do the following

1. lookup for all like variables in column A with values in column B

2. Where the values in column B are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column C

3. Where the values in column E are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column F

4. Is it possible the folumae for both values, alpha numeric (column B) and text (column E) be the same for getting the 'true or False'

Kindly help.

Thank you,

common-values-for-like-variables.xls

I need a formulae in excel which will do the following

1. lookup for all like variables in column A with values in column B

2. Where the values in column B are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column C

3. Where the values in column E are similar (samee values repeated) for all like variable, the formula in column will produce 'true' else 'False' in column F

4. Is it possible the folumae for both values, alpha numeric (column B) and text (column E) be the same for getting the 'true or False'

Kindly help.

Thank you,

common-values-for-like-variables.xls

Please see the attached. When copied to the highlighted cells, the formula in D2...

```
=COUNTIF($A$2:$A$11,$A2)-SUMPRODUCT(($A$2:$A$11=$A2)*(B$2:B$11=B2))=0
```

... works for both tests.Regards,

Brian.

common-values-for-like-variables.xls

The SUMPRODUCT formula is simply counting the number of rows where the column A value is equal to the column A value on that row....but the column B value is

Brian's version is also a valid way subtracting the SUMPRODUCT form COUNTIF and checking the result is zero is the same as

=COUNTIF($A$2:$A$11,$A2)-S

is the equivalent of

=COUNTIF($A$2:$A$11,$A2)=S

If the count of column A values that match A2 (given by COUNTIF) is the same as the number of rows that match both A2 in column A and B2 in column B (given by SUMPRODUCT) then that means that all A2s in column A must also have a B2 in column B.

The two formulas should deliver the same results

regards, barry

If you are interested, the following link to a web page of Bob Phillips, provides a lot more detail & further examples of SumProduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Barry/Brian,

I've read Bob's page before but I glanced at it again before submitting this post & noticed the following statement in the Formula Efficiency section near the bottom of the linked page. It may not have an impact in this specific example, but I thought it interesting...

=COUNTIF(A1:A10,>=10)-COUN

will be more efficient than this one,

=SUMPRODUCT((A1:A10>=10)*(

by a factor of roughly 20%.

Rob

Thanks for the note. As I think you understand, the efficiency is not relevant in this case as SumProduct's extra functionality is required. Countif doesn't allows each Variable/Value pair to be

Of course, we could have a "pure" CountIf solution, but that would have the overhead of a helper column to be added for each of the two tests (e.g. =A2&":"&B2") - so the first test would then be...

```
=COUNTIF($A$2:$A$11,A2)-COUNTIF($H$2:$H$11,H2)=0
```

... assuming the helper column is in column H. That's not as efficient as Barry's proposal (or even mine for that matter!).Regards,

Brian.

Thanks :-)

tbh, I hadn't worked the logic through completely so I wasn't sure if it would help in this case or not, but I do know with your further explanation.

Rob

Thank you for the comment and the detailed explanation.

In the below formula

SUMPRODUCT(($A$2:$A$11=$A2

=$a2 is appearing but B2 is put and not $b2.

Any particular reason why the $ is not put for B2.

thank you

["

...and just on Rob's point - you could use COUNTIFS here - in Excel 2007 and later - that would be more efficient than SUMPRODUCT also, i.e.

=COUNTIFS($A$2:$A$11,$A2,B

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

I was just about to give an answer very similar to Brian's, but then I saw his post and stopped.....but, thinking about it, perhaps this would be better as it's slightly shorter

=SUMPRODUCT(($A$2:$A$11=$A

regards, barry