AverageIFs / SumIfs

**Decar** · 12-09-2013, 02:25 PM

I tried to get familiar with arrays and those sumifs/averageifs but I cannot even do the formulas when there are 2 criteria.

Can someone show me what I did wrong?

A4:A12 = Names
B4:B12 = Numbers

I want to have the average if 2 names(criteria) are chosen

Formula:

Please Login or Register  to view this content.

But I only get #Div/0! for the Averages and 0 for the sumifs...

Averageifs.xlsx

**Ron Coderre** · 12-09-2013, 02:44 PM

The SUMIFS and AVERAGEIFS functions cannot calculate OR logic...only AND logic
Try this regular formula:

Formula:

Please Login or Register  to view this content.

or...since you really don't have multiple criteria

Formula:

Please Login or Register  to view this content.

Does that help?

**popipipo** · 12-09-2013, 02:50 PM

Or use a pivot table

**Decar** · 12-09-2013, 02:59 PM

Yes it does.

I thought they could do the OR Logic and that different criteria simply ment 2 things to search for but it seems like that only counts if they are in different columns.

Thanks for helping!

*Edit: Just saw popipipo posted as well when I hit send :D. Thanks. Pivot can do that too true but I wanted to practice my array/formula skills.

AverageIFs / SumIfs

LinkBack

Thread Tools

Rate This Thread

Display

AverageIFs / SumIfs

Re: AverageIFs / SumIfs

Re: AverageIFs / SumIfs

Re: AverageIFs / SumIfs

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] AverageIFS (or even SUMIFS/COUNTIFS) on large data set

Index with sumifs and averageifs

SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result

[SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)

AVERAGEIFS, SUMIFS and COUNTIFS Functions New functions in Excel 2007

Bookmarks

Bookmarks

Posting Permissions