Excel help

[BigBrand]

Morning guys,

If I have a colum say i2 to i20, and each cell contains one of three possible data values, is there a way to have a KEY at the side of the sheet to display the volume of each value in each cell.

e.g. colums I1 to I30 are written as :

yes
maybe
no
no
no
maybe
yes
yes
yes
maybe


.. etc.

I want a key to tell me how many YES', No's and Maybe's there are, which will also auto update when I change a value from one value to any of the other two.


Any ideas boys?

[akimba]

3 Cells with the following in

=COUNTIF(i2:i20,"Yes")
=COUNTIF(i2:i20,"No")
=COUNTIF(i2:i20,"Maybe")

[BigBrand]

Perfect!

Thanks very much. That made it a whole lot easier!

[BigBrand]

Morning,

Similar question to the one above, now I'm comfortable with the use of the above formula, I'm looking to use another one, similar to the above.

I'm needing one for value against a cell for a certain data type.

e.g.

I have say 5 different object, each object has a value, which is different. I want to make a "key" to show the combined value of each object.

Red - £5
Orange - £3
Red - £7
Blue - £2
Green - £1
Red - £3
Red - £6
Blue - £4
Red - £8


I want to present the total like

Red = X
Orange = X
Blue = X

as an accumulative total, any help please?

[akimba]

=SUMPRODUCT(--($A$1:$A$10="Red"),$B$1:$B$10)
=SUMPRODUCT(--($A$1:$A$10="Green"),$B$1:$B$10)
=SUMPRODUCT(--($A$1:$A$10="Orange"),$B$1:$B$10)
=SUMPRODUCT(--($A$1:$A$10="Blue"),$B$1:$B$10)

Can make this more dynamic by using your KEY change the "Red" to the cell ref i.e. E2 or whatever

[BigBrand]

Thanks Akimba, obviously still very new to this.

Can I confirm that say the colours were in D5 to D90 and the values were in F5 to F90 the formula would be:

=SUMPRODUCT(--($D$5:$D$90="Red"),$F$5:$F$90)
=SUMPRODUCT(--($D$5:$D$90="Blue"),$F$5:$F$90)
=SUMPRODUCT(--($D$5:$D$90="Green"),$F$5:$F$90)

Also, how can I get both these formula to work if the RESULT data is displayed don a different workbook, do I need to add something in to direct the data to check the cells in workbook A specifically?

[akimba]

Yep
I wouldn't say I was an expert in Excel (some would tho) but as a bit of advice you really don't want to start linking Excel workbooks together it can be pretty flakey :-S

[Over Carl]

Been a very long time since I actually used to piss around with excel but iirc, there was some kind of glitch with numbers not being pulled from other workbooks in some circumstances. Iirc I figured out a manual workaround (I think I incorporated the formula into a macro, and I had the macro auto refresh the relevant sheets before running the calcs)

Sorry my experience of that issue was back with Office 2003 so I have no idea whether that is still relevant and I can't even remember what the glitch actually was, and whether the bodge I mentioned above is how I actually fixed it.

[akimba]

Yeah still glitches from time to time and refreshing does fix it but it doesn't make it seamless at all.

Also the workbooks are referenced using a exact location so if you move/rename workbooks (and I know you will say you wont ever move them but it will happen) you start breaking links and when you have daisy chained 5 or 6 together(which anyone who links 2 together ultimately ends up doing) this is a ball ache.

What is it you are trying to do, if need data transferred between spreadsheets I usually load it across from one to the other before reporting on it, this way you can have the location of the workbook in a cell on a settings page (for instance) where it is easy to change etc.