CODEWITHSUNDEEP
excelif-statementexcel-formula
Anže Oberč Mrzel
Anže Oberč Mrzel

Reputation: 9

IF all cells in a range are the same, excluding blank cells

I have a table to calculate the price of a project, where one column is the description of an element of the project followed by a column for its unit (usually hours), the price per unit, number of units, and the final price, sort of like this:

enter image description here

My question really only involves the highlighted cells. Im making a template, and I add elements to the table as I go along, so some rows are left blank, but I want the total number of hours to be displayed in D7 (easy enough, =SUM(D2:D6)), but my problem is that some of the elements aren't written per hour (eg. row 4), so I want the total to only show up if all the values are for hours. Essentially:

IF all values in row B are "Hours", then sum of row D, else "")

I guess in short, is there a formula I can use that would return TRUE if all the values in a range are the same, excluding blank cells?

Thanks.

Upvotes: 0

Views: 1511

Answers (1)

JvdV
JvdV

Reputation: 75900

As per your last comment, you can try:

enter image description here enter image description here

Formula in D7:

=IF(COUNTIFS(B2:B6,"<>Hours",B2:B6,"*")=0,SUM(D2:D6),"")

Upvotes: 0

Related Questions