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.


"Asiageek" wrote:

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 a
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.