Conditional addition and averaging

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

Have a column labeled TYPE.

The potential values in TYPE are: ABC-WX, ABC-WXP, ABC-AD, ABC-ADP, XYZ-WX, XYZ-WXP, XYZ-AD, XYZ-ADP.

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

=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.

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

Rich

If the answer solves your question please tick the :heavy_check_mark:.