• Go back-and-forth until satisfied
Bing, Programming Tutor
Category: Programming
Satisfied Customers: 7514
Experience:  Microsoft Certified Solution Developer
96068672
Bing is online now

# I am trying to work out how to count a row of cells with

### Customer Question

Hi i am trying to work out how to count a row of cells with both negative and positive values
JA: Is there a specific language you're coding in?
Customer: I'm not coding, just using formulas
JA: What's your objective? How can we solve this for you?
Customer: I have different values in each cell and i want to count each cell that contains a minus value and also 0
JA: Anything else you want the programmer to know before I connect you?
Customer: No
Submitted: 9 days ago.
Category: Programming
Customer: replied 9 days ago.
Row 1 has -109.87 -109.87 0.00 0.00 0.00 0.00 and i want to put a value in another cell based on these values
Customer: replied 9 days ago.
I'm currently using this formula for the 0.00 but don't know what to add for the negative numbers
=IFS(COUNTIF(E1:H1,0)=4,1,COUNTIF(E1:H1,0)=3,2,COUNTIF(E1:H1,0)=2,3,COUNTIF(E1:H1,0)=1,4,COUNTIF(E1:H1,0)=0,5)
Expert:  homeworkhelp111 replied 9 days ago.

Hello and Welcome to JustAnswer; and thank you for posting your question. I will be happy to assist you with this.

Customer: replied 9 days ago.
Thanks
Customer: replied 9 days ago.
no thanks
Expert:  homeworkhelp111 replied 9 days ago.

How about comparing the values and input one if they are <=0 and 0 otherwise.

Then sum all the ones which will give you the count.

Expert:  homeworkhelp111 replied 9 days ago.

IF(A1<=0, 1,0)

Expert:  homeworkhelp111 replied 9 days ago.

Then sum all the values in this column which will add all the 1s and give you the count.

Customer: replied 9 days ago.
It needs to be a string of formulas that will give me for example the value of 1 based on the fact that all cells above are either a negative value or 0 in the order of the cells they appear
Customer: replied 9 days ago.
File attached (744S1T7)
Customer: replied 9 days ago.
Can you show me how the formula will look in its entirety
Expert:  homeworkhelp111 replied 9 days ago.
Have you tried this:

=IFS(COUNTIF(E11:H11,INT_MIN)=4,1,COUNTIF(E11:H11,INT_MIN)=3,2,COUNTIF(E11:H11,INT_MIN)=2,3,COUNTIF(E11:H11,INT_MIN)=1,4,COUNTIF(E11:H11,INT_MIN)=0,5)

Expert:  homeworkhelp111 replied 9 days ago.

No, wait, this will also count positive values

Customer: replied 9 days ago.
I need to include columns c and d which include the negative values in the formula
Expert:  homeworkhelp111 replied 9 days ago.

Customer: replied 9 days ago.
ok
Expert:  homeworkhelp111 replied 9 days ago.

Apologies for the delay.

Customer: replied 9 days ago.
Will it be much longer?
Expert:  Bing replied 9 days ago.

Hello and welcome to JustAnswer, an independent technical support website. My name is ***** ***** it’s my pleasure to be assisting you today.

Do you mean you need two results? The counters negative and positive numbers of a given row?

Customer: replied 9 days ago.
Expert:  Bing replied 9 days ago.

Customer: replied 9 days ago.
File attached (3LQ7T6Z)
Customer: replied 9 days ago.
Has that come through?
Expert:  Bing replied 9 days ago.

Customer: replied 9 days ago.
I am doing this as i don't know of an easier way to do it. So on sheet 2 row 1 i want to be able to look at cells c to h and because they are either 0 or minus in value then count that as 1. In all honesty there maybe a better way. If you look at sheet 1 and i have added a conditional format based on cell values. I would have preferred to have counted by colour on this sheet however because its a conditional format it won't work or so i believe
Customer: replied 9 days ago.
ideally i wanted to count the greens on Sheet 1 and then split between A/C based on my formula on Sheet 2 column O
Customer: replied 9 days ago.
then the other colours using the same process
Expert:  Bing replied 9 days ago.

Okay, I just upload an example for your reference. Just hope it could make sense to you.

Customer: replied 9 days ago.
No thats not what i'm after, i don't want to count the amount of times they appear. i was trying to have a value for each row that had those number of values appearing in the cells. If you look at sheet 1 it has 4 green entries based on the formatting i included based on those cells. I want to be able to automatically count those green rows (i.e 4) but then split them based on the A/C value in column A. So in this case any starting with ZNCL is Newcastle and Lockhart is other or customer. Does this make sense?
Customer: replied 9 days ago.
from those four green rows it splits into 1 customer and 3 Newcastle
Customer: replied 9 days ago.
then the same thing for the other formatted colours
Customer: replied 9 days ago.
Are you still there Bing?
Customer: replied 9 days ago.
Could you let me know your still on with this please as its been over half an hour