Ask Your Question
0

Conditional addition and averaging [closed]

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

richramik@yahoo.com 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 richramik@yahoo.com
close date 2017-07-20 16:31:06.354219

1 Answer

Sort by » oldest newest most voted
0

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

Comments

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

Rich

richramik@yahoo.com gravatar imagerichramik@yahoo.com ( 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

Stats

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

Seen: 41 times

Last updated: Jul 19 '17