ExcelBanter - Totalling Subtotals Less Than 1

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Totalling Subtotals Less Than 1 (https://www.excelbanter.com/excel-worksheet-functions/263842-totalling-subtotals-less-than-1-a.html)

mickey

Totalling Subtotals Less Than 1

I am trying to total subtotals, but only if they are less than 1. I've tried
doing
=SUM(G12,G24,G36,G48,G60,G72,G84,G96,G108,G120,G13 2,G144,G156,G168,G180,G192,G204,G216,G228,G240,G25 2,G264,G276,G288,G300,G312),
but it gives me an answer of -12 when I should get 18.

I've also tried
=SUMIF(I12,I24,I36,I48,I60,I72,I84,I96,I108,I120,I 132,I144,I156,I168,I180,I192,I204,I216,I228,I240,I 252,I264,I276,I288,I300,I312,"<1"),
but it results in a "you've entered too many arguments" error.

Please, please, someone help me!!! What is it that I am doing wrong?

Teethless mama

Totalling Subtotals Less Than 1

Try it like this:

=SUMPRODUCT(--(MOD(ROW(G12:G312),12)=0),--(G12:G312<1),G12:G312)

"Mickey" wrote:

I am trying to total subtotals, but only if they are less than 1. I've tried
doing
=SUM(G12,G24,G36,G48,G60,G72,G84,G96,G108,G120,G13 2,G144,G156,G168,G180,G192,G204,G216,G228,G240,G25 2,G264,G276,G288,G300,G312),
but it gives me an answer of -12 when I should get 18.

I've also tried
=SUMIF(I12,I24,I36,I48,I60,I72,I84,I96,I108,I120,I 132,I144,I156,I168,I180,I192,I204,I216,I228,I240,I 252,I264,I276,I288,I300,I312,"<1"),
but it results in a "you've entered too many arguments" error.

Please, please, someone help me!!! What is it that I am doing wrong?

T. Valko

Totalling Subtotals Less Than 1

I am trying to total subtotals, but only if they are less than 1.

Try this...

=SUMPRODUCT(--(MOD(ROW(G12:G312)-ROW(G12),12)=0),--(G12:G312<1),G12:G312)

--
Biff
Microsoft Excel MVP

"Mickey" wrote in message
...
I am trying to total subtotals, but only if they are less than 1. I've
tried
doing
=SUM(G12,G24,G36,G48,G60,G72,G84,G96,G108,G120,G13 2,G144,G156,G168,G180,G192,G204,G216,G228,G240,G25 2,G264,G276,G288,G300,G312),
but it gives me an answer of -12 when I should get 18.

I've also tried
=SUMIF(I12,I24,I36,I48,I60,I72,I84,I96,I108,I120,I 132,I144,I156,I168,I180,I192,I204,I216,I228,I240,I 252,I264,I276,I288,I300,I312,"<1"),
but it results in a "you've entered too many arguments" error.

Please, please, someone help me!!! What is it that I am doing wrong?

All times are GMT +1. The time now is 02:20 PM.