Combining SUMIF and Subtotal for a single column of numbers

**mosesthetank** · 03-20-2014, 03:35 PM

I have a column of numbers with several subtotals. At the bottom of the column I want to use a SUMIF to total the column but ignore the Subtotals and all negative numbers. I attached an example file with the desired result.

Thanks!

**Jonmo1** · 03-20-2014, 03:37 PM

Try
=SUMIFS(B1:B14,B1:B14,">0",A1:A14,"<>Ttl")

**AZ-XL** · 03-20-2014, 03:52 PM

Alternative solution
=SUBTOTAL(9,B1:B14)-SUMIF(B1:B14,"<0")

**mosesthetank** · 03-20-2014, 04:03 PM

Jonmo1 and AZ-XL. Both of your solutions work. I went with AZ-XLs because the text in column A is unreliable. Thanks to both of you!

**AZ-XL** · 03-20-2014, 04:05 PM

You are welcome. Thanx for feedback

**daddylonglegs** · 03-20-2014, 04:15 PM

Originally Posted by AZ-XL

=SUBTOTAL(9,B1:B14)-SUMIF(B1:B14,"<0")

This may not work if any of the subtotals are negative

Combining SUMIF and Subtotal for a single column of numbers

LinkBack

Thread Tools

Rate This Thread

Display

Combining SUMIF and Subtotal for a single column of numbers

Re: Combining SUMIF and Subtotal for a single column of numbers

Re: Combining SUMIF and Subtotal for a single column of numbers

Re: Combining SUMIF and Subtotal for a single column of numbers

Re: Combining SUMIF and Subtotal for a single column of numbers

Re: Combining SUMIF and Subtotal for a single column of numbers

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Combining numbers in a column into one cell

[SOLVED] Combining multiple columns of data into single column

Combining a table column with another range into a single array

Combining sumif, subtotal with filters?

Combining SUMIF and SUBTOTAL functions

Bookmarks

Bookmarks

Posting Permissions