Research by matrix

[Sabine]

Hi,

Forgiveness in advance for my English ...

In column A, dates (dtS), in B names (prN) and C numerical values (vaL),
like example below:
01/01/2008 PIERRE 3
01/01/2008 LUC 4
01/01/2008 PAUL
02/01/2008 PAUL 7
02/01/2008 PIERRE
03/01/2008 PAUL 8
03/01/2008 PIERRE
05/01/2008 LUC 8
05/01/2008 PIERRE 1

I would like a formula in a single cell (matrix) which I counted the number
of lines having the same date in column A on the same line
names research "Pierre or Luc" in column B and numerical value in column C.
In my example, the formula would result in 2 because on line 1 and 2, 8 and
9, we find the same date for Pierre or Luc in column A, Pierre or Luc in
column B, and a numerical value in front in column C of Pierre and Luc.

For conditional formatting, it would be something like:
=(NB.SI(dtS;$A2)>1)*(($B2="pierre")+($B2="luc"))*($C2<>"")

I tried this, but that does not give the expected results:
{=SOMME((NB.SI(dtS;dtS)>1)*((prN="pierre")+(prN="luc"))*(vaL<>""))}
;-(

Thanks in advance for your help,


Sabine

 

[Bernie Deitrick]

Sabine,

I don't think you can get what you want by a single matrix formula, since you have mulitple matrices
that require checking.

Based on your data table being sorted by column A ascending, I would use a column of formulas like
this, starting in cell D2, and copied down.

=IF(A1<>A2,IF((SUMPRODUCT((Dts=A2)*(prN="Pierre")*(vaL<>""))+SUMPRODUCT((Dts=A2)*(prN="Luc")*(vaL<>"")))=2,1,""),"")

Then sum that column.

I'm not sure what SUMPRODUCT is in French, but maybe you already do...

HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

Try this:

=SUM(IF(FREQUENCY(IF((prN={"PIERRE","LUC"})*(vaL<>""),MATCH(dtS,dtS,0)),MATCH(dtS,dtS,0))>0,1))

ctrl+shift+enter, not just enter

 

[Pete_UK]

SUMPRODUCT is SOMMEPROD in French, and IF is SI.

Hope this helps.

Pete

 

[Sabine]

Thanks to all of your solutions, which I have been able to adapt to my
problem.
Have a good day.

Sabine