Forum

Notifications
Clear all

Aggregate Function with IF Condition

5 Posts
4 Users
0 Reactions
862 Views
(@shaakir725)
Posts: 15
Eminent Member
Topic starter
 

Hi,

Aggregate funtion with IF conditions doesn't work for options like Average and Sum. But it works fine for options Large and Small. Any concerns! Screenshot-2023-01-12-102647.png

Screenshot-2023-01-12-102614.png

 
Posted : 13/01/2023 12:57 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

Please attach an Excel file instead of pictures, your formulas are referencing columns we don't see in the pictures and not many want to sit and create their own sample data, which may or may not be similar to your data.

There is a nice blog post about this aggregate function, well worth reading.
https://www.myonlinetraininghub.com/excel-aggregate-function

As per the help text about this function ( https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df)

Errors:
If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error.
If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.

Without access to the data I can't really give a better answer.

Br,
Anders

 
Posted : 14/01/2023 11:09 am
(@jstewart)
Posts: 216
Estimable Member
 

It looks like you have the order wrong, it should be IF(D:D="Full Time", AGGREGATE(...),""). As Anders said, we can't give a better answer without seeing the actual spreadsheet.

 
Posted : 14/01/2023 11:38 am
(@debaser)
Posts: 838
Member Moderator
 

The first 13 options for AGGREGATE require a range to process and will not work with an array, which is what your IF function is returning.

 
Posted : 14/01/2023 11:48 am
(@shaakir725)
Posts: 15
Eminent Member
Topic starter
 

Thank you for the reply!

Pls check the google sheets. Pls feel free to edit the google sheets, I have given edit access.

https://docs.google.com/spreadsheets/d/1O3HwRh2E-uNXR7j_48ROpuey1GfQnq_M/edit?usp=sharing&ouid=107824383430170858468&rtpof=true&sd=true, https://docs.google.com/spreadsheets/d/1WMf5gA0AcJl2jBuT4KYajDLs1x9juA_f/edit?usp=sharing&ouid=107824383430170858468&rtpof=true&sd=true

 
Posted : 15/01/2023 2:19 am
Share: