Ask Your Question

Conditional addition and averaging [closed]

asked 2017-07-19 21:23:47 +0200 gravatar image

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

edit retag flag offensive reopen merge delete

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

1 Answer

Sort by » oldest newest most voted

answered 2017-07-19 22:55:09 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more


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

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

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2017-07-21 10:28:04 +0200 )edit

Question Tools

1 follower


Asked: 2017-07-19 21:23:47 +0200

Seen: 41 times

Last updated: Jul 19 '17