I have one column of numerical data, ten in number, containing both positive and negative numbers, as well as zeros/blanks. In the adjacent column, I have the letter "c" in some but not all of the adjacent cells to the first column. Where there is no "c", the cell is blank.
I would like to have a formula that sums all of the positive numbers where there is a "c" adjacent and a formula that will do the same for negative numbers.
Thanks in advance.
Something like this:
=SUMIFS(A:A,A:A,"<"&0,B:B,"c")
=SUMIFS(A:A,A:A,">"&0,B:B,"c")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks, although I just realized I asked for the wrong result.
What I am really after is a formula that will sum all of the positive numbers in column 1 that have adjacent cells that contain a zero or are blank in column 2; same for negatives.
Sorry, and thanks again.
This, then?
=SUMIFS(A:A,A:A,"<"&0,B:B,"")+SUMIFS(A:A,A:A,">"&0,B:B,"")+SUMIFS(A:A,A:A,"<"&0,B:B,0)+SUMIFS(A:A,A:A,">"&0,B:B,0)
I entered this exact formula, and the result was the TOTAL of ALL of the cells in column 1 that have adjacent cells with a "c", both positive and negative.
What I am after is 2 formulas: one that will sum all of the positive numbers in the first column that have adjacent cells in column 2 with "c"s, and another that will do the same for all of the negative numbers in column 1.
Sorry I wasnt more clear.
Well, that’s what the first pair I offered do. We seem to be going round in circles.
Thanks, I thought they all went together!
I'm extremely confused - is this solved or not?
Yes, Im sorry. Thank you again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks