# Conditional addition and averaging [closed]

Here we go. Have to explain since I can’t upload a segment of the sheet.

Have a column labeled TYPE.

I have another column labeled DAYS TO APRV. Each of the corresponding lines in TYPE, may have a value if the item in TYPE has actually been APPROVED.

What I am trying to calculate is the 1) the total number of entries that have ABC in it and 2) the total number of lines that have XYZ.

I then want to calculate the average days it took to approve the entries for ABC, but only if it has a value (APPROVED value). The same for XYZ.

I hope I have explained this clearly enough.

I have played with AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS, COUNTIF and COUNTIFS. The statements became quite ugly. I have tried using the wildcards (.*) but with no success.

Thanks, Rich Ramik

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by richramik@yahoo.com close date 2017-07-20 16:31:06.354219

Sort by » oldest newest most voted

=COUNTIFS(range;"ABC.*") should work with regular expressions.
=COUNTIFS(range;"ABC*") should work with wildcards.

From 5.2, we have the option for 'regular expession' or 'wildcards', the last activated by default when a xls or xlsx file is opened and 'wildcards' will be by default with version 6.

more

This is what I was trying to do. It does what I want. Thank you very much.

Rich

( 2017-07-20 16:30:31 +0200 )edit