COUNTIF - SUMIF don't appear to work in this sheet.

[Guest]

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue item is
added to the Sales Sheet via combo/lists? Each time a new item is listed, it
will automatically appear in the tally list as a new item and/or count tally
of appearences sorted by ranking. There are 870 product items (column A) with
their price (column B) from the Data Sheet though commonly only a maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
...
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
...
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.

 

[Guest]

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that have
Blue. try adding to the worksheet =len(A13) which will return the number of
characters at A13. This should return a 4. If it is not 4, then you havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In
the From box type one space. Then replace all.

 

[Guest]

yep, that works, had spaces on the end of the data to be counted somehow when
filtering however, what do i do about all those products listed (blue,
red...) as there are 870 of them. Blue red etc are just examples of a product.

Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with
870 products do not need to be entered into the formula?

The name of the product and how many times it appears in the list needs to
be displayed in the tally. The name isn't appearing only next to the count in
it's own table.

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that have
Blue. try adding to the worksheet =len(A13) which will return the number of
characters at A13. This should return a 4. If it is not 4, then you havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In
the From box type one space. Then replace all.

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue item is
added to the Sales Sheet via combo/lists? Each time a new item is listed, it
will automatically appear in the tally list as a new item and/or count tally
of appearences sorted by ranking. There are 870 product items (column A) with
their price (column B) from the Data Sheet though commonly only a maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
..
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
..
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.

 

[Guest]

You can concatenate text in a formula

=CONCATENATE("Number of Blue entries = ",TEXT(COUNTIF(A10:A91,"Blue")))

yep, that works, had spaces on the end of the data to be counted somehow when
filtering however, what do i do about all those products listed (blue,
red...) as there are 870 of them. Blue red etc are just examples of a product.

Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with
870 products do not need to be entered into the formula?

The name of the product and how many times it appears in the list needs to
be displayed in the tally. The name isn't appearing only next to the count in
it's own table.

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that have
Blue. try adding to the worksheet =len(A13) which will return the number of
characters at A13. This should return a 4. If it is not 4, then you havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In
the From box type one space. Then replace all.

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue item is
added to the Sales Sheet via combo/lists? Each time a new item is listed, it
will automatically appear in the tally list as a new item and/or count tally
of appearences sorted by ranking. There are 870 product items (column A) with
their price (column B) from the Data Sheet though commonly only a maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
..
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
..
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.

 

[Don Guillett]

=SUMPRODUCT((A10:A91={"red","blue","green"})*1)
--
Don Guillett
SalesAid Software
(e-mail address removed)

yep, that works, had spaces on the end of the data to be counted somehow
when
filtering however, what do i do about all those products listed (blue,
red...) as there are 870 of them. Blue red etc are just examples of a
product.

Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with
870 products do not need to be entered into the formula?

The name of the product and how many times it appears in the list needs to
be displayed in the tally. The name isn't appearing only next to the count
in
it's own table.

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that
have
Blue. try adding to the worksheet =len(A13) which will return the number
of
characters at A13. This should return a 4. If it is not 4, then you
havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace.
In
the From box type one space. Then replace all.

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List
[Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but
didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue
item is
added to the Sales Sheet via combo/lists? Each time a new item is
listed, it
will automatically appear in the tally list as a new item and/or count
tally
of appearences sorted by ranking. There are 870 product items (column
A) with
their price (column B) from the Data Sheet though commonly only a
maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
..
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
..
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as
exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do
not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.

 

[Don Guillett]

to sum col B from that criteria
=SUMPRODUCT((A10:A91={"red","blue","green"})*b10:b91)

--
Don Guillett
SalesAid Software
(e-mail address removed)

yep, that works, had spaces on the end of the data to be counted somehow
when
filtering however, what do i do about all those products listed (blue,
red...) as there are 870 of them. Blue red etc are just examples of a
product.

Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with
870 products do not need to be entered into the formula?

The name of the product and how many times it appears in the list needs to
be displayed in the tally. The name isn't appearing only next to the count
in
it's own table.

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that
have
Blue. try adding to the worksheet =len(A13) which will return the number
of
characters at A13. This should return a 4. If it is not 4, then you
havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace.
In
the From box type one space. Then replace all.

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List
[Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but
didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue
item is
added to the Sales Sheet via combo/lists? Each time a new item is
listed, it
will automatically appear in the tally list as a new item and/or count
tally
of appearences sorted by ranking. There are 870 product items (column
A) with
their price (column B) from the Data Sheet though commonly only a
maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
..
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
..
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as
exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do
not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.

 

[Guest]

Thanks a lot!
This works just fine. It counts the products (without summing them).

Thanks again, cya

You can concatenate text in a formula

=CONCATENATE("Number of Blue entries = ",TEXT(COUNTIF(A10:A91,"Blue")))

yep, that works, had spaces on the end of the data to be counted somehow when
filtering however, what do i do about all those products listed (blue,
red...) as there are 870 of them. Blue red etc are just examples of a product.

Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with
870 products do not need to be entered into the formula?

The name of the product and how many times it appears in the list needs to
be displayed in the tally. The name isn't appearing only next to the count in
it's own table.

Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets.

=COUNTIF(A10:A91,"Blue")

to actualy sum the answers then use Sum if

=SUMIF(A10:A91,"Blue",B10:B91)

Note: check to see if there are any blank characters in the cells that have
Blue. try adding to the worksheet =len(A13) which will return the number of
characters at A13. This should return a 4. If it is not 4, then you havve
some spaces in your data.


To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In
the From box type one space. Then replace all.


:

I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales
Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't
seem to work at all on the v/data sheet unless I stuffed up, but where?

How do i calculate how many "Blue" items are listed each time a Blue item is
added to the Sales Sheet via combo/lists? Each time a new item is listed, it
will automatically appear in the tally list as a new item and/or count tally
of appearences sorted by ranking. There are 870 product items (column A) with
their price (column B) from the Data Sheet though commonly only a maximum of
10 should appear in the tally next to the data sold.

eg. The data sold input using validation list/combobox (Sales Sheet).
A B... K L
11 Blue $50
12 Red $40
13 Blue $50
14 Orange $20
15 Blue $50
16 Orange $20
..
91

In this example above, there are:
3x Blue, 1x Red, 2x Orange.

I require the tally list to look like this alphabeticalised by name:

ie. The tally list K-L.
A B... K L
11 Blue $50 Blue 3
12 Red $40 Orange 2
13 Blue $50 Red 1
14 Orange $20 .. ..
15 Blue $50
16 Orange $20
..
91

When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no
results (0) are returned from the range of data
[=COUNTIF(A10:B91,"Blue")].

Is this because of v/data or I'm just doin' it wrong? Also, surely I do not
need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same
formula?

Thanks a lot.