I`ve spent a few hours making this formel work for 2007. This formel works
for 2006, and I cant figure out why its giving me a hard time. Is it the
formel or could it be the worksheet?

=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Dat a!$G$2:$H$655360;IF(Data!$F$2:$F$65536=$A3;IF(YEA R(INT(Data!$I$2:$I$65536))=2007;IF(INT(Data!$E$2:$ E$65536)<=INT(Data!$I$2:$I$65536);Data!$G$2:$H$655 36))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

What is the error/problem with the formula?
--
Brevity is the soul of wit.


"svemor" wrote:

I`ve spent a few hours making this formel work for 2007. This formel works
for 2006, and I cant figure out why its giving me a hard time. Is it the
formel or could it be the worksheet?

=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Dat a!$G$2:$H$655360;IF(Data!$F$2:$F$65536=$A3;IF(YEA R(INT(Data!$I$2:$I$65536))=2007;IF(INT(Data!$E$2:$ E$65536)<=INT(Data!$I$2:$I$65536);Data!$G$2:$H$655 36))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

Sorry...

It returns 0. But there has been sales that the formula should have counted.

/svemor

"Dave F" wrote:

What is the error/problem with the formula?
--
Brevity is the soul of wit.


"svemor" wrote:

I`ve spent a few hours making this formel work for 2007. This formel works
for 2006, and I cant figure out why its giving me a hard time. Is it the
formel or could it be the worksheet?

=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Dat a!$G$2:$H$655360;IF(Data!$F$2:$F$65536=$A3;IF(YEA R(INT(Data!$I$2:$I$65536))=2007;IF(INT(Data!$E$2:$ E$65536)<=INT(Data!$I$2:$I$65536);Data!$G$2:$H$655 36))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

I would try something like this:

=COUNTIF(Data!$B$2:$B$65536=Statistikk!B$2)+COUNTI F(Data!$G$2:$H$655360)+COUNTIF(Data!$F$2:$F$65536 =$A3)+COUNTIF(YEAR(INT(Data!$I$2:$I$65536))=2007)+ COUNTIF(INT(Data!$E$2:$E$65536)<=INT(Data!$I$2:$I$ 65536))

I'm not sure what this portion of your formula is supposed to do:
;Data!$G$2:$H$65536))))))


Dave
--
Brevity is the soul of wit.


"svemor" wrote:

Sorry...

It returns 0. But there has been sales that the formula should have counted.

/svemor

"Dave F" wrote:

What is the error/problem with the formula?
--
Brevity is the soul of wit.


"svemor" wrote:

I`ve spent a few hours making this formel work for 2007. This formel works
for 2006, and I cant figure out why its giving me a hard time. Is it the
formel or could it be the worksheet?

=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Dat a!$G$2:$H$655360;IF(Data!$F$2:$F$65536=$A3;IF(YEA R(INT(Data!$I$2:$I$65536))=2007;IF(INT(Data!$E$2:$ E$65536)<=INT(Data!$I$2:$I$65536);Data!$G$2:$H$655 36))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

The logic of your formula, with the successive IF's, shows that you
want all conditions to hold and then count the records satisfying the
conditions. The following formula is equivalent.

=SUMPRODUCT((Data!$B$2:$B$65536=Statistikk!B$2)*(D ata!$G$2:$H$655360)*(Data!$F$2:$F$65536=$A3)*(YEA R(INT(Data!$I$2:$I$65536))=2007)*(INT(Data!$E$2:$E $65536)<=INT(Data!$I$2:$I$65536)))


Try it and see if it produces the same or other result as a starting
point. If it still does not work properly something is wrong with the
data (e.g. cells that hold text while you think they are holding a
date).

HTH
Kostis Vezerides


svemor wrote:
I`ve spent a few hours making this formel work for 2007. This formel works
for 2006, and I cant figure out why its giving me a hard time. Is it the
formel or could it be the worksheet?

=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Dat a!$G$2:$H$655360;IF(Data!$F$2:$F$65536=$A3;IF(YEA R(INT(Data!$I$2:$I$65536))=2007;IF(INT(Data!$E$2:$ E$65536)<=INT(Data!$I$2:$I$65536);Data!$G$2:$H$655 36))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

svemor wrote...
....
=COUNT(IF(Data!$B$2:$B$65536=Statistikk!B$2;IF(Da ta!$G$2:$H$655360;
IF(Data!$F$2:$F$65536=$A3;IF(YEAR(INT(Data!$I$2:$ I$65536))=2007;
IF(INT(Data!$E$2:$E$65536)<=INT(Data!$I$2:$I$6553 6);Data!$G$2:$H$65536))))))

Data in B column = name of saleperson
Data in G column = Sale amount
Data in F column = weeknumber
Data in I column = Sale date
Data in E column = Date of contact from saleperson
Data in G/H column = Sale amount

Difficult to imagine that E# I# for any # in 2..65536 shouldn't have
generated an error upon entry. Since that's fundamental, I'd put the
array formula

=AND(Data!$E$2:$E$65536<=Data!$I$2:$I$65536)

in another cell (call it VALID), then wrap the replacement for the
formula above inside

=IF(VALID;...;"Invalid")

As for the formula above, easier to use

=IF(VALID;
SUMPRODUCT((Data!$B$2:$B$65536=Statistikk!B$2)*(Da ta!$G$2:$H$655360)
*(Data!$F$2:$F$65536=$A3)*(YEAR(Data!$I$2:$I$65536 )=2007)*Data!$G$2:$H$65536;
"Invalid")

Note that I deleted all INT calls. These are date values, and as long
as Excel displays them as dates, they're positive numbers. As positive
numbers, YEAR(INT(x)) is *ALWAYS* equal to YEAR(x). The INT calls serve
no clear purpose. As for comparing columns E and I, calling INT would
exclude all sales made on the same day as the date of contact by the
salesperson, but would include sales made, say, 2 minutes after
midnight when the date and time of contact were, say, 5 minutes before
that midnight. I'm guessing that's not what you intended.