|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Hi,
I’m hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
I’ll try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella
"T. Valko" wrote:
> This will "COUNTIF" B2:B100 = "A" in a filtered list:
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "tommy" <tommy@discussions.microsoft.com> wrote in message
> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
> > do anybody know, how to use "countif" with filtered lists?
> >
> > the function subtotal does not allow conditions (e.g. "name")
> > the function countif does it, but it count hidden cells, too
> >
> > thank you for your ideas
> >
> > "N Harkawat" wrote:
> >
> >> =subtotal(2,a1:a1000)
> >>
> >> "Counting filtered data." <Counting filtered
> >> data.@discussions.microsoft.com> wrote in message
> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
> >> >I have an excel spread sheet table that I filter. I would like to be
> >> >able
> >> >to
> >> > count the number of cells in the filtered data. Anyone know how to do
> >> > this?
> >> > CountA returns the number of cells in the unfiltered data.
> >> > Tom
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
>In both columns data must be chosen
>from a drop down menu
Is that a data validation drop down list (or maybe a combo box) or is it an
Auto Filter drop down?
--
Biff
Microsoft Excel MVP
"Antonella" <Antonella@discussions.microsoft.com> wrote in message
news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
> Hi,
>
> I'm hoping that someone can help me..
> I am trying to count how many export has been done for each Region for
> July,
> how many for August and so on.
> I'll try to make myself a bit clear. For example:
> My first column (A1:A100) contains 10 different Regions (Netherlands,
> Italy,
> Spain etc). The second column (B1:B100) contains months ( July, August,
> September, October etc). I would like to know if there is formula to count
> how many times Netherlands July appears in those 2 columns.
> In both columns data must be chosen from a drop down menu..
> Hope I made myself clear.. Can this be done ?
> Thanks
> Antonella
>
> "T. Valko" wrote:
>
>> This will "COUNTIF" B2:B100 = "A" in a filtered list:
>>
>> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "tommy" <tommy@discussions.microsoft.com> wrote in message
>> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
>> > do anybody know, how to use "countif" with filtered lists?
>> >
>> > the function subtotal does not allow conditions (e.g. "name")
>> > the function countif does it, but it count hidden cells, too
>> >
>> > thank you for your ideas
>> >
>> > "N Harkawat" wrote:
>> >
>> >> =subtotal(2,a1:a1000)
>> >>
>> >> "Counting filtered data." <Counting filtered
>> >> data.@discussions.microsoft.com> wrote in message
>> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
>> >> >I have an excel spread sheet table that I filter. I would like to be
>> >> >able
>> >> >to
>> >> > count the number of cells in the filtered data. Anyone know how to
>> >> > do
>> >> > this?
>> >> > CountA returns the number of cells in the unfiltered data.
>> >> > Tom
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Is a data validation drop down list. Can be the reason why does not work?
"T. Valko" wrote:
> >In both columns data must be chosen
> >from a drop down menu
>
> Is that a data validation drop down list (or maybe a combo box) or is it an
> Auto Filter drop down?
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
> > Hi,
> >
> > I'm hoping that someone can help me..
> > I am trying to count how many export has been done for each Region for
> > July,
> > how many for August and so on.
> > I'll try to make myself a bit clear. For example:
> > My first column (A1:A100) contains 10 different Regions (Netherlands,
> > Italy,
> > Spain etc). The second column (B1:B100) contains months ( July, August,
> > September, October etc). I would like to know if there is formula to count
> > how many times Netherlands July appears in those 2 columns.
> > In both columns data must be chosen from a drop down menu..
> > Hope I made myself clear.. Can this be done ?
> > Thanks
> > Antonella
> >
> > "T. Valko" wrote:
> >
> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
> >>
> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
> >> > do anybody know, how to use "countif" with filtered lists?
> >> >
> >> > the function subtotal does not allow conditions (e.g. "name")
> >> > the function countif does it, but it count hidden cells, too
> >> >
> >> > thank you for your ideas
> >> >
> >> > "N Harkawat" wrote:
> >> >
> >> >> =subtotal(2,a1:a1000)
> >> >>
> >> >> "Counting filtered data." <Counting filtered
> >> >> data.@discussions.microsoft.com> wrote in message
> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
> >> >> >I have an excel spread sheet table that I filter. I would like to be
> >> >> >able
> >> >> >to
> >> >> > count the number of cells in the filtered data. Anyone know how to
> >> >> > do
> >> >> > this?
> >> >> > CountA returns the number of cells in the unfiltered data.
> >> >> > Tom
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
>Is a data validation drop down list.
Try this:
=SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
Better to use cells to hold the criteria.
D1 = Netherlands
E1 = July
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
--
Biff
Microsoft Excel MVP
"Antonella" <Antonella@discussions.microsoft.com> wrote in message
news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
> Is a data validation drop down list. Can be the reason why does not work?
>
> "T. Valko" wrote:
>
>> >In both columns data must be chosen
>> >from a drop down menu
>>
>> Is that a data validation drop down list (or maybe a combo box) or is it
>> an
>> Auto Filter drop down?
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
>> > Hi,
>> >
>> > I'm hoping that someone can help me..
>> > I am trying to count how many export has been done for each Region for
>> > July,
>> > how many for August and so on.
>> > I'll try to make myself a bit clear. For example:
>> > My first column (A1:A100) contains 10 different Regions (Netherlands,
>> > Italy,
>> > Spain etc). The second column (B1:B100) contains months ( July, August,
>> > September, October etc). I would like to know if there is formula to
>> > count
>> > how many times Netherlands July appears in those 2 columns.
>> > In both columns data must be chosen from a drop down menu..
>> > Hope I made myself clear.. Can this be done ?
>> > Thanks
>> > Antonella
>> >
>> > "T. Valko" wrote:
>> >
>> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
>> >>
>> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
>> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
>> >> > do anybody know, how to use "countif" with filtered lists?
>> >> >
>> >> > the function subtotal does not allow conditions (e.g. "name")
>> >> > the function countif does it, but it count hidden cells, too
>> >> >
>> >> > thank you for your ideas
>> >> >
>> >> > "N Harkawat" wrote:
>> >> >
>> >> >> =subtotal(2,a1:a1000)
>> >> >>
>> >> >> "Counting filtered data." <Counting filtered
>> >> >> data.@discussions.microsoft.com> wrote in message
>> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
>> >> >> >I have an excel spread sheet table that I filter. I would like to
>> >> >> >be
>> >> >> >able
>> >> >> >to
>> >> >> > count the number of cells in the filtered data. Anyone know how
>> >> >> > to
>> >> >> > do
>> >> >> > this?
>> >> >> > CountA returns the number of cells in the unfiltered data.
>> >> >> > Tom
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Thanks.. You made my day!!!
Antonella
"T. Valko" wrote:
> >Is a data validation drop down list.
>
> Try this:
>
> =SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
>
> Better to use cells to hold the criteria.
>
> D1 = Netherlands
> E1 = July
>
> =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
> > Is a data validation drop down list. Can be the reason why does not work?
> >
> > "T. Valko" wrote:
> >
> >> >In both columns data must be chosen
> >> >from a drop down menu
> >>
> >> Is that a data validation drop down list (or maybe a combo box) or is it
> >> an
> >> Auto Filter drop down?
> >>
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
> >> > Hi,
> >> >
> >> > I'm hoping that someone can help me..
> >> > I am trying to count how many export has been done for each Region for
> >> > July,
> >> > how many for August and so on.
> >> > I'll try to make myself a bit clear. For example:
> >> > My first column (A1:A100) contains 10 different Regions (Netherlands,
> >> > Italy,
> >> > Spain etc). The second column (B1:B100) contains months ( July, August,
> >> > September, October etc). I would like to know if there is formula to
> >> > count
> >> > how many times Netherlands July appears in those 2 columns.
> >> > In both columns data must be chosen from a drop down menu..
> >> > Hope I made myself clear.. Can this be done ?
> >> > Thanks
> >> > Antonella
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
> >> >>
> >> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
> >> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
> >> >> > do anybody know, how to use "countif" with filtered lists?
> >> >> >
> >> >> > the function subtotal does not allow conditions (e.g. "name")
> >> >> > the function countif does it, but it count hidden cells, too
> >> >> >
> >> >> > thank you for your ideas
> >> >> >
> >> >> > "N Harkawat" wrote:
> >> >> >
> >> >> >> =subtotal(2,a1:a1000)
> >> >> >>
> >> >> >> "Counting filtered data." <Counting filtered
> >> >> >> data.@discussions.microsoft.com> wrote in message
> >> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
> >> >> >> >I have an excel spread sheet table that I filter. I would like to
> >> >> >> >be
> >> >> >> >able
> >> >> >> >to
> >> >> >> > count the number of cells in the filtered data. Anyone know how
> >> >> >> > to
> >> >> >> > do
> >> >> >> > this?
> >> >> >> > CountA returns the number of cells in the unfiltered data.
> >> >> >> > Tom
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Antonella" <Antonella@discussions.microsoft.com> wrote in message
news:CAF11EE1-94F3-46DA-BE81-D35AADF12DAB@microsoft.com...
> Thanks.. You made my day!!!
> Antonella
>
> "T. Valko" wrote:
>
>> >Is a data validation drop down list.
>>
>> Try this:
>>
>> =SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
>>
>> Better to use cells to hold the criteria.
>>
>> D1 = Netherlands
>> E1 = July
>>
>> =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
>> > Is a data validation drop down list. Can be the reason why does not
>> > work?
>> >
>> > "T. Valko" wrote:
>> >
>> >> >In both columns data must be chosen
>> >> >from a drop down menu
>> >>
>> >> Is that a data validation drop down list (or maybe a combo box) or is
>> >> it
>> >> an
>> >> Auto Filter drop down?
>> >>
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> >> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I'm hoping that someone can help me..
>> >> > I am trying to count how many export has been done for each Region
>> >> > for
>> >> > July,
>> >> > how many for August and so on.
>> >> > I'll try to make myself a bit clear. For example:
>> >> > My first column (A1:A100) contains 10 different Regions
>> >> > (Netherlands,
>> >> > Italy,
>> >> > Spain etc). The second column (B1:B100) contains months ( July,
>> >> > August,
>> >> > September, October etc). I would like to know if there is formula to
>> >> > count
>> >> > how many times Netherlands July appears in those 2 columns.
>> >> > In both columns data must be chosen from a drop down menu..
>> >> > Hope I made myself clear.. Can this be done ?
>> >> > Thanks
>> >> > Antonella
>> >> >
>> >> > "T. Valko" wrote:
>> >> >
>> >> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
>> >> >>
>> >> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
>> >> >>
>> >> >> --
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >>
>> >> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
>> >> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
>> >> >> > do anybody know, how to use "countif" with filtered lists?
>> >> >> >
>> >> >> > the function subtotal does not allow conditions (e.g. "name")
>> >> >> > the function countif does it, but it count hidden cells, too
>> >> >> >
>> >> >> > thank you for your ideas
>> >> >> >
>> >> >> > "N Harkawat" wrote:
>> >> >> >
>> >> >> >> =subtotal(2,a1:a1000)
>> >> >> >>
>> >> >> >> "Counting filtered data." <Counting filtered
>> >> >> >> data.@discussions.microsoft.com> wrote in message
>> >> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
>> >> >> >> >I have an excel spread sheet table that I filter. I would like
>> >> >> >> >to
>> >> >> >> >be
>> >> >> >> >able
>> >> >> >> >to
>> >> >> >> > count the number of cells in the filtered data. Anyone know
>> >> >> >> > how
>> >> >> >> > to
>> >> >> >> > do
>> >> >> >> > this?
>> >> >> >> > CountA returns the number of cells in the unfiltered data.
>> >> >> >> > Tom
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi I need your help..
I know that is not the right forum but I'll try anyway.
Hope you will understand what I’m trying to do.
I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM.
I’ve linked few cells from Monthly to Total (that’s was easy). Now all the
formula already in Total give Error as results.
Any help greatly appreciated.
Antonella
"T. Valko" wrote:
> You're welcome. Thanks for the feedback!
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> news:CAF11EE1-94F3-46DA-BE81-D35AADF12DAB@microsoft.com...
> > Thanks.. You made my day!!!
> > Antonella
> >
> > "T. Valko" wrote:
> >
> >> >Is a data validation drop down list.
> >>
> >> Try this:
> >>
> >> =SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
> >>
> >> Better to use cells to hold the criteria.
> >>
> >> D1 = Netherlands
> >> E1 = July
> >>
> >> =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
> >> > Is a data validation drop down list. Can be the reason why does not
> >> > work?
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> >In both columns data must be chosen
> >> >> >from a drop down menu
> >> >>
> >> >> Is that a data validation drop down list (or maybe a combo box) or is
> >> >> it
> >> >> an
> >> >> Auto Filter drop down?
> >> >>
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> >> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I'm hoping that someone can help me..
> >> >> > I am trying to count how many export has been done for each Region
> >> >> > for
> >> >> > July,
> >> >> > how many for August and so on.
> >> >> > I'll try to make myself a bit clear. For example:
> >> >> > My first column (A1:A100) contains 10 different Regions
> >> >> > (Netherlands,
> >> >> > Italy,
> >> >> > Spain etc). The second column (B1:B100) contains months ( July,
> >> >> > August,
> >> >> > September, October etc). I would like to know if there is formula to
> >> >> > count
> >> >> > how many times Netherlands July appears in those 2 columns.
> >> >> > In both columns data must be chosen from a drop down menu..
> >> >> > Hope I made myself clear.. Can this be done ?
> >> >> > Thanks
> >> >> > Antonella
> >> >> >
> >> >> > "T. Valko" wrote:
> >> >> >
> >> >> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
> >> >> >>
> >> >> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
> >> >> >>
> >> >> >> --
> >> >> >> Biff
> >> >> >> Microsoft Excel MVP
> >> >> >>
> >> >> >>
> >> >> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
> >> >> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
> >> >> >> > do anybody know, how to use "countif" with filtered lists?
> >> >> >> >
> >> >> >> > the function subtotal does not allow conditions (e.g. "name")
> >> >> >> > the function countif does it, but it count hidden cells, too
> >> >> >> >
> >> >> >> > thank you for your ideas
> >> >> >> >
> >> >> >> > "N Harkawat" wrote:
> >> >> >> >
> >> >> >> >> =subtotal(2,a1:a1000)
> >> >> >> >>
> >> >> >> >> "Counting filtered data." <Counting filtered
> >> >> >> >> data.@discussions.microsoft.com> wrote in message
> >> >> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
> >> >> >> >> >I have an excel spread sheet table that I filter. I would like
> >> >> >> >> >to
> >> >> >> >> >be
> >> >> >> >> >able
> >> >> >> >> >to
> >> >> >> >> > count the number of cells in the filtered data. Anyone know
> >> >> >> >> > how
> >> >> >> >> > to
> >> >> >> >> > do
> >> >> >> >> > this?
> >> >> >> >> > CountA returns the number of cells in the unfiltered data.
> >> >> >> >> > Tom
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Your syntax is not correct.
You cannot have Total.xls and Monthly.xls in one workbook.
An *.xls is a single workbook.
Do you mean you have two worksheets named Total and Monthly in a workbook
named PM.xls
Is this latest problem you are posting in any way related to the original
posting about counting filtered items you and Biff have been working on?
Gord Dibben MS Excel MVP
On Thu, 6 Aug 2009 10:10:15 -0700, Antonella
<Antonella@discussions.microsoft.com> wrote:
>Hi I need your help..
>I know that is not the right forum but I'll try anyway.
>Hope you will understand what I’m trying to do.
>I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM.
>I’ve linked few cells from Monthly to Total (that’s was easy). Now all the
>formula already in Total give Error as results.
>Any help greatly appreciated.
>Antonella
>
>
>
>"T. Valko" wrote:
>
>> You're welcome. Thanks for the feedback!
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> news:CAF11EE1-94F3-46DA-BE81-D35AADF12DAB@microsoft.com...
>> > Thanks.. You made my day!!!
>> > Antonella
>> >
>> > "T. Valko" wrote:
>> >
>> >> >Is a data validation drop down list.
>> >>
>> >> Try this:
>> >>
>> >> =SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
>> >>
>> >> Better to use cells to hold the criteria.
>> >>
>> >> D1 = Netherlands
>> >> E1 = July
>> >>
>> >> =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> >> news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
>> >> > Is a data validation drop down list. Can be the reason why does not
>> >> > work?
>> >> >
>> >> > "T. Valko" wrote:
>> >> >
>> >> >> >In both columns data must be chosen
>> >> >> >from a drop down menu
>> >> >>
>> >> >> Is that a data validation drop down list (or maybe a combo box) or is
>> >> >> it
>> >> >> an
>> >> >> Auto Filter drop down?
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >>
>> >> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
>> >> >> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
>> >> >> > Hi,
>> >> >> >
>> >> >> > I'm hoping that someone can help me..
>> >> >> > I am trying to count how many export has been done for each Region
>> >> >> > for
>> >> >> > July,
>> >> >> > how many for August and so on.
>> >> >> > I'll try to make myself a bit clear. For example:
>> >> >> > My first column (A1:A100) contains 10 different Regions
>> >> >> > (Netherlands,
>> >> >> > Italy,
>> >> >> > Spain etc). The second column (B1:B100) contains months ( July,
>> >> >> > August,
>> >> >> > September, October etc). I would like to know if there is formula to
>> >> >> > count
>> >> >> > how many times Netherlands July appears in those 2 columns.
>> >> >> > In both columns data must be chosen from a drop down menu..
>> >> >> > Hope I made myself clear.. Can this be done ?
>> >> >> > Thanks
>> >> >> > Antonella
>> >> >> >
>> >> >> > "T. Valko" wrote:
>> >> >> >
>> >> >> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
>> >> >> >>
>> >> >> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
>> >> >> >>
>> >> >> >> --
>> >> >> >> Biff
>> >> >> >> Microsoft Excel MVP
>> >> >> >>
>> >> >> >>
>> >> >> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
>> >> >> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
>> >> >> >> > do anybody know, how to use "countif" with filtered lists?
>> >> >> >> >
>> >> >> >> > the function subtotal does not allow conditions (e.g. "name")
>> >> >> >> > the function countif does it, but it count hidden cells, too
>> >> >> >> >
>> >> >> >> > thank you for your ideas
>> >> >> >> >
>> >> >> >> > "N Harkawat" wrote:
>> >> >> >> >
>> >> >> >> >> =subtotal(2,a1:a1000)
>> >> >> >> >>
>> >> >> >> >> "Counting filtered data." <Counting filtered
>> >> >> >> >> data.@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
>> >> >> >> >> >I have an excel spread sheet table that I filter. I would like
>> >> >> >> >> >to
>> >> >> >> >> >be
>> >> >> >> >> >able
>> >> >> >> >> >to
>> >> >> >> >> > count the number of cells in the filtered data. Anyone know
>> >> >> >> >> > how
>> >> >> >> >> > to
>> >> >> >> >> > do
>> >> >> >> >> > this?
>> >> >> >> >> > CountA returns the number of cells in the unfiltered data.
>> >> >> >> >> > Tom
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Yep I've got 2 worksheets linked to each other. How can I make a formula
works if the cells I need to calculate are linked to the first worksheet.
Hope do you get my point?
"Gord Dibben" wrote:
> Your syntax is not correct.
>
> You cannot have Total.xls and Monthly.xls in one workbook.
>
> An *.xls is a single workbook.
>
> Do you mean you have two worksheets named Total and Monthly in a workbook
> named PM.xls
>
> Is this latest problem you are posting in any way related to the original
> posting about counting filtered items you and Biff have been working on?
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 6 Aug 2009 10:10:15 -0700, Antonella
> <Antonella@discussions.microsoft.com> wrote:
>
> >Hi I need your help..
> >I know that is not the right forum but I'll try anyway.
> >Hope you will understand what I’m trying to do.
> >I’ve got 2 worksheets called Total.xls Monthly.xls in a workbook called PM.
> >I’ve linked few cells from Monthly to Total (that’s was easy). Now all the
> >formula already in Total give Error as results.
> >Any help greatly appreciated.
> >Antonella
> >
> >
> >
> >"T. Valko" wrote:
> >
> >> You're welcome. Thanks for the feedback!
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> news:CAF11EE1-94F3-46DA-BE81-D35AADF12DAB@microsoft.com...
> >> > Thanks.. You made my day!!!
> >> > Antonella
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> >Is a data validation drop down list.
> >> >>
> >> >> Try this:
> >> >>
> >> >> =SUMPRODUCT(--(A1:A100="Netherlands"),--(B1:B10="July"))
> >> >>
> >> >> Better to use cells to hold the criteria.
> >> >>
> >> >> D1 = Netherlands
> >> >> E1 = July
> >> >>
> >> >> =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> >> news:C4A6024B-5EFC-4099-AF90-5AAFDFCDAAAD@microsoft.com...
> >> >> > Is a data validation drop down list. Can be the reason why does not
> >> >> > work?
> >> >> >
> >> >> > "T. Valko" wrote:
> >> >> >
> >> >> >> >In both columns data must be chosen
> >> >> >> >from a drop down menu
> >> >> >>
> >> >> >> Is that a data validation drop down list (or maybe a combo box) or is
> >> >> >> it
> >> >> >> an
> >> >> >> Auto Filter drop down?
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Biff
> >> >> >> Microsoft Excel MVP
> >> >> >>
> >> >> >>
> >> >> >> "Antonella" <Antonella@discussions.microsoft.com> wrote in message
> >> >> >> news:E524FB16-EC23-40E2-A066-4CA0D3410097@microsoft.com...
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I'm hoping that someone can help me..
> >> >> >> > I am trying to count how many export has been done for each Region
> >> >> >> > for
> >> >> >> > July,
> >> >> >> > how many for August and so on.
> >> >> >> > I'll try to make myself a bit clear. For example:
> >> >> >> > My first column (A1:A100) contains 10 different Regions
> >> >> >> > (Netherlands,
> >> >> >> > Italy,
> >> >> >> > Spain etc). The second column (B1:B100) contains months ( July,
> >> >> >> > August,
> >> >> >> > September, October etc). I would like to know if there is formula to
> >> >> >> > count
> >> >> >> > how many times Netherlands July appears in those 2 columns.
> >> >> >> > In both columns data must be chosen from a drop down menu..
> >> >> >> > Hope I made myself clear.. Can this be done ?
> >> >> >> > Thanks
> >> >> >> > Antonella
> >> >> >> >
> >> >> >> > "T. Valko" wrote:
> >> >> >> >
> >> >> >> >> This will "COUNTIF" B2:B100 = "A" in a filtered list:
> >> >> >> >>
> >> >> >> >> =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100="A"))
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Biff
> >> >> >> >> Microsoft Excel MVP
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "tommy" <tommy@discussions.microsoft.com> wrote in message
> >> >> >> >> news:D75D8700-C5D1-4ACD-9576-4CF9E0B6F48F@microsoft.com...
> >> >> >> >> > do anybody know, how to use "countif" with filtered lists?
> >> >> >> >> >
> >> >> >> >> > the function subtotal does not allow conditions (e.g. "name")
> >> >> >> >> > the function countif does it, but it count hidden cells, too
> >> >> >> >> >
> >> >> >> >> > thank you for your ideas
> >> >> >> >> >
> >> >> >> >> > "N Harkawat" wrote:
> >> >> >> >> >
> >> >> >> >> >> =subtotal(2,a1:a1000)
> >> >> >> >> >>
> >> >> >> >> >> "Counting filtered data." <Counting filtered
> >> >> >> >> >> data.@discussions.microsoft.com> wrote in message
> >> >> >> >> >> news:E31F9B08-CDC1-4BF4-BAB9-ED56589806DD@microsoft.com...
> >> >> >> >> >> >I have an excel spread sheet table that I filter. I would like
> >> >> >> >> >> >to
> >> >> >> >> >> >be
> >> >> >> >> >> >able
> >> >> >> >> >> >to
> >> >> >> >> >> > count the number of cells in the filtered data. Anyone know
> >> >> >> >> >> > how
> >> >> >> >> >> > to
> >> >> >> >> >> > do
> >> >> >> >> >> > this?
> >> >> >> >> >> > CountA returns the number of cells in the unfiltered data.
> >> >> >> >> >> > Tom
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Answer
Notify me of replies