How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • Go back-and-forth until satisfied
    Rate the answer you receive.
By continuing to use this site you consent to the use of cookies on your device as described in our cookie policy unless you have disabled them.
Ask Bing Your Own Question
Bing
Bing, Programming Tutor
Category: Programming
Satisfied Customers: 7514
Experience:  Microsoft Certified Solution Developer
96068672
Type Your Programming Question Here...
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.

Ask Your Own Programming Question
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.

Ask Your Own Programming Question
Expert:  homeworkhelp111 replied 9 days ago.

IF(A1<=0, 1,0)

Ask Your Own Programming Question
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.

Ask Your Own Programming Question
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)

Ask Your Own Programming Question
Expert:  homeworkhelp111 replied 9 days ago.

No, wait, this will also count positive values

Ask Your Own Programming Question
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.

Sorry, I am not sure about this. Let me open this question to other experts.

Ask Your Own Programming Question
Customer: replied 9 days ago.
ok
Expert:  homeworkhelp111 replied 9 days ago.

Apologies for the delay.

Ask Your Own Programming Question
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?

Ask Your Own Programming Question
Customer: replied 9 days ago.
Hi Bing, are able to see the spreadsheet i added?
Expert:  Bing replied 9 days ago.

No. I see nothing uploaded.

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

Yes, please advise which range is to be counted?

Ask Your Own Programming Question
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.

Ask Your Own Programming Question
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