Conditional Sum in Excel that counts values above X as X

0 votes
In Excel, I have a column of values which I want to sum, but I want any values above 5 to only count as 5. I can do this by having another column that changes the value to 5 if it's above 5 and then summing that column

=IF(C4>5,5,C4)

Is there a way to do this with a sumif statement or some other way to avoid having a new column?
Mar 28 in Database by Edureka
• 13,640 points
54 views

1 answer to this question.

0 votes
B1 formula is:

=SUMIF(A1:A5,"<=5")

+COUNTIF(A1:A5,">5")

*5 Fill in the blanks with the image's description.

The following is an array-entered variant:

=SUM(IF(A1:A5>5,5,A1:A5))
answered Mar 31 by gaurav
• 22,040 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 13,640 points
785 views
0 votes
0 answers

Merging Two excel files as two sheets in one workbook in java

I have two xlsx files at folder ...READ MORE

Mar 10 in Database by Edureka
• 13,640 points
650 views
0 votes
1 answer

How to count distinct values in Excel

Use functions to count the number of ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
528 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Remove special characters from the specified string in excel

To erase a specific character from a ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
599 views
0 votes
1 answer

Remove duplicates within a row

To check for duplicates, choose the cells ...READ MORE

answered Mar 15 in Database by gaurav
• 22,040 points
82 views
0 votes
1 answer
0 votes
1 answer

Group by Sum in Excel

It is very easy and you can ...READ MORE

answered Feb 21 in Database by gaurav
• 22,040 points
137 views
0 votes
1 answer

Merging Two excel files as two sheets in one workbook in java

Basically for this, you need to create ...READ MORE

answered Feb 21 in Database by gaurav
• 22,040 points
179 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP