Research by matrix
 

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="l uc"))*(vaL<""))}
;-(

Thanks in advance for your help,


Sabine

Research by matrix
 

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


"Sabine" wrote in message ...
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="l uc"))*(vaL<""))}
;-(

Thanks in advance for your help,


Sabine

Research by matrix
 

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


"Sabine" wrote:

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="l uc"))*(vaL<""))}
;-(

Thanks in advance for your help,


Sabine

Research by matrix
 

SUMPRODUCT is SOMMEPROD in French, and IF is SI.

Hope this helps.

Pete

On Apr 11, 3:39*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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((Dt*s=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



"Sabine" wrote in . ..
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="l uc"))*(vaL<""))}
;-(

Thanks in advance for your help,

Sabine- Hide quoted text -

- Show quoted text -

Research by matrix
 

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

Sabine