if or sumif help required for a 7 cell range

[acana007]

here is what i am looking to do.

for the cell range of eg. A1:A7

I would like to take each cell, and if it is equal to or less then 0 to ignore it, then for all the cells that are More then 5. I would like to add all that up and take the value from each cell and subtract 8. so that it only gives me the total value of what is over 5. I hope you understand what I am trying to get at.

let me try to explain it this way

if cell A1=0 A2=7 A3=8 A4=6 A5=9 A6=6 A7=0 my total that I want to see is 11 not 36.

thanks in advance for you assistance.

[dosydos]

2 questions..
1. Do you want the sum of everything equal to and greater than 5, or just over 5?
2. You confused me with your explanation and desired results. Do you want to subtract 8 from every number greater than and equal to 5?

I included a formula that sums all numbers greater than and equal to 5, and then counts how many numbers are greater than and equal to 5 and subtracts 8 from each of them.

=SUMIF($A$1:$A$7,">=5",$A$1:$A$7)-COUNTIF($A$1:$A$7,">=5")*8

[dosydos]

I just reread and i think you meant to say the difference over 5 not 8. in which case this formula will work.

=SUMIF($C$1:$I$1,">=5",$C$1:$I$1)-COUNTIF($C$1:$I$1,">=5")*5

If you want greater than 5 instead of greater than or equal to 5 then change ">=" to ">" in the above formula. it will look like this shown below...

=SUMIF($C$1:$I$1,">5",$C$1:$I$1)-COUNTIF($C$1:$I$1,">5")*5


EDIT: Changed cell references to C1:I1

[acana007]

thanks but I had meant to say c1 to I1 if thats possible.

[63falcondude]

Hello and welcome to the forum.

The logic here is not clear.

...if it is equal to or less then 0 to ignore it, then for all the cells that are More then 5...

So you want to ignore anything ≤ 0.
What about anything that is > 0 and ≤ 5?

...then for all the cells that are More then 5. I would like to add all that up and take the value from each cell and subtract 8.

Subtract 8 from each cell that is > 5 or subtract 8 from the total of the cells that are > 5?

if cell A1=0 A2=7 A3=8 A4=6 A5=9 A6=6 A7=0 my total that I want to see is 11 not 36.

Please show us the math behind how you get to 11 based on this example.

[dosydos]

Acana please try the formulas in my Post #3. I have updated them to include your new range C1:I1

[acana007]

Thanks Dosydos your first formula is exactly what I had wanted. =SUMIF($C$1:$I$1,">=5",$C$1:$I$1)-COUNTIF($C$1:$I$1,">=5")*5 is the answer I was looking for.

however could you go into a little further detail on this answer so I can understand it and further my knowledge about formuals. please and thank you

[acana007]

for example why the $c$1 is what i mean?

[63falcondude]

I just reread and i think you meant to say the difference over 5 not 8.

Good catch.

[dosydos]

=SUMIF($C$1:$I$1,">=5",$C$1:$I$1)-COUNTIF($C$1:$I$1,">=5")*5

Its easiest to look at this formula in 2 parts. Part1 is a Sumif() and Part 2 is a countif().

=SUMIF($C$1:$I$1,">=5",$C$1:$I$1)
Description:This formula is summing numbers in C1:I1 that are greater than or equal to 5. Anything less than 5 is ignored.

The first portion of the formula is the range $C$1:$I$1 in which we are applying our criteria ">=5" too.
The middle section ">=5" is the criteria"
And finally the last section are the cells we would like to sum, which happens to be the same as our original range $C$1:$I$1.

-COUNTIF($C$1:$I$1,">=5")*5
Description:This formula is counting how many numbers are greater than or equal to 5. Then taking this amount and multiplying by 5.

First section is the range we would like to count $C$1:$I$1.
Second section is the criteria ">=5" stating count the amount of cells greater than or equal to 5.
Finally the *5 is multiplying the amount of numbers over 5 by 5.

We then take this total and subtract it from the orginal sum.

[acana007]

Thanks again

[dosydos]

Glad to help. Thanks for the rep

[acana007]

I do have one other question that kinda go's hand in hand with this formula that you have helped me on do I post it here or as a new topic

[dosydos]

you can post right here, whats the question

[acana007]

so I have to formulas on each sheet, and I have multiple sheets a total of 10. my formulas are in cells G27 and G29 of each sheet and I want to find which is greater and pull that across to the first sheet. so would it be something like =SUM('sheet2:sheet10' G27< G29)

[dosydos]

what would your desired results look like?
Will the largest number in all of the 10 G27's be pulled to the first page, and the largest of the 10 G29's pulled to the front page as well?
or
would there be 10 results on the first page where you are taking the larger number of G27 and G29 for each sheet and putting all 10 results on the first page.

I need more clarification.

[acana007]

so lets say on page 2 g27 = 4 and g29 =10 and on page 3 g27 = 2 and g29 = 15 and page 4 g27 = 8 and g29 = 2. then the total value i would like to have on page 1 is g29 from page 2 add g29 from page 3 and add g27 from page 4 which would equal 33.

[dosydos]

Its not the prettiest but it will do the job.
Put this fomula in your sheet1. This takes which ever value is greater in G27 or G29 for each sheet and adds them together.
Right now the formula is going after the worksheets Sheet2 - Sheet10. Change these names if different. If you have more worksheets that you want it to include add them to the formula. It is repeating the same logic over and over for each sheet so you should be able to figure the trend out with a little time and patience.

Please Login or Register  to view this content.

[acana007]

thanks that helped alot. I verified it by hand just to make sure.