using Averageifs formula with Defined Name Manager

Hi there,

How can you averageifs with sales greater than zero for a specific vendor.

This formula shows #VALUE! but I believe it's correct.

i.e. =AVERAGEIFS(SALES,VENDOR_NAME,B4,SALES,">0")

Any help would be greatly appreciated

Thank you.

#Value suggests data problem although AVERAGEIFS deals with many data problems OK.

SALES/VENDOE_NAME are named range

Your formula should work so without data it's difficult to give a definite answer.

Is it possible to post a sample value with data giving error?

You can also get a #Value! error if the ranges are not equally dimensioned.

What is in the 'refers to' box for each named range?

Hi everyone,

I am able to sum and average but not average greater than 0.

Not sure why the formula is not working...it seems like a simple formula.



Attachment 453812



I have attached a sample data.

never mind.. incorrect response.

It's because the ranges are not equal dimensions.

Sales has 22 rows <- shown by =ROWS(Sales)
Vendor_Name has 23 rows <- shown by =ROWS(Vendor_Name)

This is caused by one of the cells (B5) on the Data Sheet in column B that appears to be blank, really isn't blank.
So it's being counted by the COUNTA function in your named range definition.

Right click that cell, and clear contents.

However, a better solution to avoid having to hunt down the one off cell causing an issue with named ranges like that.

When using dynamic named ranges, I suggest making one name that counts how many rows are in the data set.
Then use that same one in all of your dynamic named ranges.

Example
Create the names
RwCount: =COUNTA(DATA!$D:$D)-1
Sales: =OFFSET(DATA!$D$8,,,RwCount)
Vendor_Name: =OFFSET(DATA!$A$8,,,RwCount)


This way, if the count is off in RwCount, at least all ranges are still the same dimension
(better for them both to be too large but the same, than 1 to be correct and the other too large)

Hi Jonmo1,

I selected on cell B5 on the data sheet and cleared the cell, and it automatically populated!! Yay!!!!

I'm not sure what "equal dimensions" mean, but I will have to figure that out and research it on my own time.

I have also created the name, "RwCount", and used your formula...and it also worked too!!! Double Yay!!!

Now I have to wrap my head around and understand the formula.

I have marked this thread as "Solved"
and I will Click on the star that you have helped me

Thank you & have a great day

You're welcome.

equal dimensions basically means "Same Size and Shape".
The Same number of Columns AND the same number of Rows.

Your named range Sales, was 1 column and 22 Rows
Your named range Vendor_Name was 1 column and 23 Rows

Those are unequal dimensions.