Excel 2003 - averageif or sumif?

=SUMIF(A2:A4,"=Name1",B2:B4)/SUMPRODUCT(IF(A2:A4="Name1",1,0)*IF(B2:B4="",0,1))
It's an array formula so you need to confirm with ctrl+shift+enter

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thank you for your fast reply

This works fine when all info is on the same sheet, but when I try to get the average from another sheet it doesn't work, I get the #NUM! error

lets say the example sheet is called "example", I am trying:

=SUMIF('example'!A2:A4;"=Name 1";'1'!B2:B4)/SUMPRODUCT(IF('example'!A2:A4="Name 1";1;0)*IF('example'!B2:B4="";0;1)) with the ctrl+shift+enter confirmation.

like this I get the mentioned error.

any way to get around this?

Thanks again

Can you upload a workbook with the formula and data as it is providing an error?

Sorry, this is for my work and am not allowed to share this data, but I would like to.

Replace any sensitive data with dummy data and provide an example of the formula not working. I tried the formula as you posted and it did not give me a #NUM! error. It is possible that it is an issue with your data?

Remove all private and confidential informations. Change names into xxxx and pirices into 1234 etc. Delete all extra contents that's not important for your problem.

Originally Posted by daddylonglegs

Are you trying to get data from a single sheet or do you want to work out an average across all 31 sheets?

From sheet 1 to sheet 31.

I try: ('1:[31]31'!D:D) to get the info.

Originally Posted by yudlugar

Replace any sensitive data with dummy data and provide an example of the formula not working. I tried the formula as you posted and it did not give me a #NUM! error. It is possible that it is an issue with your data?

I tried it again and it worked, I selected the whole column (A:A) and (B:B) now changed to (A1:A150) and (B1:B150), now I just need to get the average from all the sheets (1 to 31), tried with:

=SUMIF('1:4'!B1:B150;"=n_1";'1:4'!C1:C150)/SUMPRODUCT(IF('1:4'!B1:B150="n_1";1;0)*IF('1:4'!C1:C150="";0;1))

like this I get the #VALUE! error

Thanks for all the help