formula to solve

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns with information which I need to collate.

The first column has variables such as apples, grapes, pears
The second column rates the first column from 1 to 5.

I want to count how many "1" ratings I get for apples and how many "2"
ratings and so on for each of the items in the first column.

I think I should be using the “countif†function together with “andâ€
function but I just can’t work it out.

Any ideas would be very helpful. Thanks
 
Ben said:
I have two columns with information which I need to collate.

The first column has variables such as apples, grapes, pears
The second column rates the first column from 1 to 5.

I want to count how many "1" ratings I get for apples and how many "2"
ratings and so on for each of the items in the first column.

I think I should be using the “countif†function together with “andâ€
function but I just can’t work it out.

Any ideas would be very helpful. Thanks
Click to expand...

I'd recommend using a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

But if you want to do it with formulae, try:

=SUMPRODUCT(--(A1:A1000="apples"),--(B1:B100=1))

See

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

for an explanation.
 
Do you want this as a little table, with Apples, Grapes, Pears etc
going down column A (in a separate sheet) and 1, 2, 3 etc going across
row 1 ?

If so, then ensure you have a header row in your first sheet, eg
"Variable" in A1 and "Rating" in B1, and insert a new worksheet.
Highlight all the data and the heading in column A of Sheet1 and copy
it to column A of Sheet2. While the data is still highlighted, click
on Data | Filter | Advanced filter and in the pop-up click on Unique
Records Only, and Copy to another location - enter C1 in the Copy To
box and click OK.

You will now have a unique list of your variables in column C of
Sheet2, and you can delete columns A and B so that the variables list
is now in column A. Enter 1 to 5 in B1 to F1, and in B2 enter this
formula:

=SUMPRODUCT((Sheet1!$A$2:$A$500=$A2)*(Sheet1!$B$2:$B$500=B$1))

This assumes you have 500 rows in Sheet1 - adjust as necessary, but
make sure both arrays are the same size. Copy the formula into C2:F2,
and then copy B2:F2 down for as many items as you have in column A.

You now have your ratings table against each variable. In a case like
this you might have a number of zeros, and you can hide these if you
like by highlighting from B2 down to F-whatever and clicking Format |
Conditional Formatting and select Cell Contents in the first panel,
Equal to in the second panel and 0 (zero) in the 3rd panel. Then click
on the Format button and select Colour and set to white, then OK twice
to get out of this. This will now show all zero values as white on
white, effectively making the cells appear blank to allow the rating
numbers to stand out.

Hope this helps.

Pete
 
Back
Top