countif

[Roger]

Hi there!
I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.

Anyone?
regards,

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article <[email protected]>,
"Roger" <[email protected]> wrote:

> I have a spreadsheet wiht 5 colums; I want to count how many times a product
> appears on column E, only IF cells on column A= 1000.

[Roger]

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

> One way:
>
> =SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))
>
> http://www.mcgimpsey.com/excel/doubleneg.html
>
>
> In article <[email protected]>,
> "Roger" <[email protected]> wrote:
>
> > I have a spreadsheet wiht 5 colums; I want to count how many times a product
> > appears on column E, only IF cells on column A= 1000.
>

[Dave Peterson]

Don't put the formula in A1:A2000 and not in E1:E2000.

But do make sure you change "<product>" to the real name.

If this doesn't help, post back the formula you used.

Roger wrote:
>
> Hi there,
>
> Many thanks for your help, but the formula is returning wiht 0(zero), value
> - Is there a specific place I should write the formula?
> regards,
> Roger
>
> "JE McGimpsey" wrote:
>
> > One way:
> >
> > =SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))
> >
> > http://www.mcgimpsey.com/excel/doubleneg.html
> >
> >
> > In article <[email protected]>,
> > "Roger" <[email protected]> wrote:
> >
> > > I have a spreadsheet wiht 5 colums; I want to count how many times a product
> > > appears on column E, only IF cells on column A= 1000.
> >

--

Dave Peterson

[Roger]

Once again many thanks for your help.

The formula I have used is :
=SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
The result in the cell is zero, however when I press the fx on the formula
bar, the correct number appears.

Best regards,
Roger

"Dave Peterson" wrote:

> Don't put the formula in A1:A2000 and not in E1:E2000.
>
> But do make sure you change "<product>" to the real name.
>
> If this doesn't help, post back the formula you used.
>
> Roger wrote:
> >
> > Hi there,
> >
> > Many thanks for your help, but the formula is returning wiht 0(zero), value
> > - Is there a specific place I should write the formula?
> > regards,
> > Roger
> >
> > "JE McGimpsey" wrote:
> >
> > > One way:
> > >
> > > =SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))
> > >
> > > http://www.mcgimpsey.com/excel/doubleneg.html
> > >
> > >
> > > In article <[email protected]>,
> > > "Roger" <[email protected]> wrote:
> > >
> > > > I have a spreadsheet wiht 5 colums; I want to count how many times a product
> > > > appears on column E, only IF cells on column A= 1000.
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Do you have calculation set to automatic?

Tools|Options|Calculation tab


Roger wrote:
>
> Once again many thanks for your help.
>
> The formula I have used is :
> =SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
> The result in the cell is zero, however when I press the fx on the formula
> bar, the correct number appears.
>
> Best regards,
> Roger
>
> "Dave Peterson" wrote:
>
> > Don't put the formula in A1:A2000 and not in E1:E2000.
> >
> > But do make sure you change "<product>" to the real name.
> >
> > If this doesn't help, post back the formula you used.
> >
> > Roger wrote:
> > >
> > > Hi there,
> > >
> > > Many thanks for your help, but the formula is returning wiht 0(zero), value
> > > - Is there a specific place I should write the formula?
> > > regards,
> > > Roger
> > >
> > > "JE McGimpsey" wrote:
> > >
> > > > One way:
> > > >
> > > > =SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))
> > > >
> > > > http://www.mcgimpsey.com/excel/doubleneg.html
> > > >
> > > >
> > > > In article <[email protected]>,
> > > > "Roger" <[email protected]> wrote:
> > > >
> > > > > I have a spreadsheet wiht 5 colums; I want to count how many times a product
> > > > > appears on column E, only IF cells on column A= 1000.
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Roger]

Dave,

Its ok now...not sure what happened! Anyway...thank you ever so much for
your help and time!
Best Regards,
Roger

"Dave Peterson" wrote:

> Do you have calculation set to automatic?
>
> Tools|Options|Calculation tab
>
>
> Roger wrote:
> >
> > Once again many thanks for your help.
> >
> > The formula I have used is :
> > =SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
> > The result in the cell is zero, however when I press the fx on the formula
> > bar, the correct number appears.
> >
> > Best regards,
> > Roger
> >
> > "Dave Peterson" wrote:
> >
> > > Don't put the formula in A1:A2000 and not in E1:E2000.
> > >
> > > But do make sure you change "<product>" to the real name.
> > >
> > > If this doesn't help, post back the formula you used.
> > >
> > > Roger wrote:
> > > >
> > > > Hi there,
> > > >
> > > > Many thanks for your help, but the formula is returning wiht 0(zero), value
> > > > - Is there a specific place I should write the formula?
> > > > regards,
> > > > Roger
> > > >
> > > > "JE McGimpsey" wrote:
> > > >
> > > > > One way:
> > > > >
> > > > > =SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product>"))
> > > > >
> > > > > http://www.mcgimpsey.com/excel/doubleneg.html
> > > > >
> > > > >
> > > > > In article <[email protected]>,
> > > > > "Roger" <[email protected]> wrote:
> > > > >
> > > > > > I have a spreadsheet wiht 5 colums; I want to count how many times a product
> > > > > > appears on column E, only IF cells on column A= 1000.
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>