Altering values of one cell in increments based on the value of cells in a row

[Murchie]

I'm a little bit stuck trying to create a formula for a spreadsheet that I am working on.

I am trying to simplify data I have collected in a table into two cells, what I need to do is have the value of the cell I have marked x to increase by +1 (starting from 0) for each green cell with a 1 in it on each row (the x value for each week is based on the number of green cells with a 1 in them on that row)

I also would like for the value of the cell I have marked y to increase by +1 (starting from 0) for each red cell with a 2 in it on each row. The cells that are coloured blue with no number inside of them I would like the formula to ignore. White cells haven't been updated yet as I have to update the spreadsheet weekly.

Any help on this would be greatly appreciated

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Could you post again using the above, but also include the expected results, entered manually?

Also, all of your "1" cells are green and all of your "2" cells are red, so do we actually have to consider the colour, or just the value?

 

[Murchie]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Could you post again using the above, but also include the expected results, entered manually?

Also, all of your "1" cells are green and all of your "2" cells are red, so do we actually have to consider the colour, or just the value?

Hi there, thank you for your quick reply,

I have tried to use the add in however I get a message stating that "microsoft has blocked macros from running because the source of the file is untrusted"

In regards to my question, the cell colour isn't important as the colour is just to make the table look better, the value is the only relevant part for the formula, i have edited the x and y values to what i would expect the results to be for the formula.

 

[Murchie]

Hi there, thank you for your quick reply,

I have tried to use the add in however I get a message stating that "microsoft has blocked macros from running because the source of the file is untrusted"

In regards to my question, the cell colour isn't important as the colour is just to make the table look better, the value is the only relevant part for the formula, i have edited the x and y values to what i would expect the results to be for the formula.

I've got the add in working now so i will repost my original question

 

[Fluff]

Duplicate to: Altering values of one cell in increments based on the value of cells in a row

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.