Issues with AGGREGATE function

[hgalstyan]

I am trying to use Aggregate function to sum up the rows in M column, referencing to A column (to the rows, where the cell starts with "O" letter. Here is the formula: =+AGGREGATE(9,2,(A7:A112="O*")*M7:M112).
The result is #VALUE! error. Is there an alternative way to to this? Sumproduct, sum+if, sum(sumif and other formulas do not work, as I need to ignore errors and nested subtotals in M column.

Great thanks to anyone who tries to help.

Regards,
Hrayr

 

[Weazel]

if i'm understanding correctly maybe....

=SUMIF(A7:A112,"o*",M7:M112)

 

[hgalstyan]

if i'm understanding correctly maybe....

=SUMIF(A7:A112,"o*",M7:M112)

No dear, I need to apply all the "weapons" of Aggregate function.

 

[XOR LX]

Are you trying to use the asterisk as a wildcard there, i.e. column A entries should begin with the letter O?

If so, you won't be able to within AGGREGATE. More importantly, you can only use conditional clauses (e.g. (A7:A112="O") within that function if the function_num parameter is 14 or greater.

I would suggest switching to an array formula** with SUBTOTAL:

=SUM(IF(SUBTOTAL(2,OFFSET(M7,ROW(M7:M192)-MIN(ROW(M7:M192)),)),IF(LEFT(A7:A192)="O",M7:M192)),)

which will also ignore nested subtotals and errors within the range M7:M192.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[hgalstyan]

Are you trying to use the asterisk as a wildcard there, i.e. column A entries should begin with the letter O?

If so, you won't be able to within AGGREGATE. More importantly, you can only use conditional clauses (e.g. (A7:A112="O") within that function if the function_num parameter is 14 or greater.

I would suggest switching to an array formula** with SUBTOTAL:

=SUM(IF(SUBTOTAL(2,OFFSET(M7,ROW(M7:M192)-MIN(ROW(M7:M192)),)),IF(LEFT(A7:A192)="O",M7:M192)),)

which will also ignore nested subtotals and errors within the range M7:M192.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Thanks for reply, but this array formula returns #N/A

 

[XOR LX]

Thanks for reply, but this array formula returns #N/A

I don't see how that is possible, unless you also have errors in column A which you didn't tell us about.

Regards