Maybe a loop?

Tucker92

Board Regular
Joined
Jun 7, 2018
Messages
53
Hi everyone,

I was wondering if there was a way that i would be able to add up some numbers based on whether or not there was a letter in the cell below the cell i want to include.

Currently i have a range of numbers but i need to be able to mark them in some way if they are above or below the average, this will be done manually and i was after a loop or something to go through my data and each time it finds a character record the number above and total them together into a cell i choose.

Is this possible?
Bit of a noob when it comes to VBA
 
Last edited:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
922
Office Version
2007
Platform
Windows
Hi there. Rather than manually marking up above or below the average, you could do it all in one formula. Put this formula in the cell where you want the above average sum: =SUMIF(A1:H1,">"&AVERAGE(A1:H1))

A1:H1 is the range of values, so adjust to suit. To get below average, use =SUMIF(A1:H1,"<"&AVERAGE(A1:H1)), you may want to put an equals sign in as well (i.e. >= or <=) to catch any exact matches.
 

Tucker92

Board Regular
Joined
Jun 7, 2018
Messages
53
Im happy with the manual input as the numbers are timings and its just going to be the ones that are really far out. I have a timer on a userform that is used as a stopwatch and it will only be when there is an issue in the work area that the number will be far out and will easily be noticed.

I am just after a way of totaling up the marked items to be inputted into the another cell as i need an overall ineffective time of the bad ones

I can use this in another of my spreadsheets and will help alot
thank you
 
Last edited:

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
922
Office Version
2007
Platform
Windows
OK thats fine. In that case, you need this formula in your desired result cell: =SUMIF(A2:H2,"n",A1:H1)
This sums all the numbers in range A1:H1 if the equivalent value in range A2:H2 (i.e. the row underneath) is an n.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
922
Office Version
2007
Platform
Windows
You're welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,066
Messages
5,412,141
Members
403,418
Latest member
Parky4002

This Week's Hot Topics

Top