Prev: countif/sumproduct + month
Next: Conditional Formatting - stuck on formula
From: fam on 3 May 2010 15:46
I have amount due in column G, who owes amount in column H, who provided the
service in column f.

I need to be able to Filter on column f, total amount due in column G, based
on who owes in column H.

I wrote a formula which works for totals in column G based on column H, but
it includes all service providers and I want it to only include the items I
have filtered for. =SUMIFS(G7:G1448,H7:H1448,1)

Currently Column G subtotals when I filter on column F. I need subtotals
for column G based on who owes in Column H.
From: fam on 3 May 2010 18:09
C F G H
A Jim 5 1
B Jimmy 6 2
C John 5 2
A Joe 6 1

Column C is the company a service was provided for.
Column F is the person who provided the service.
Column G is amount due
Column H designates if the company pays me directly (2) or if the company
paid the service provider who then pays me (1).
Currently I provide the company with the details of what they owe my by
filtering on Col C and then sbutotal Col G for companys' amount due.

I want to find an efficient way to be able to provide the service provider
with the details of how much he owes me and how much I directly collected
from companies where he provided the service.
Currently I filter on F (service provider) and use the following formula in
2 separate cells:
=SUMIFS($G$7:$G$1448,$F$7:$F$1448,"Jim",$H$7:$H$1448,1)
=SUMIFS($G$7:$G$1448,$F$7:$F$1448,"Jim",$H$7:$H$1448,2)
The problem is: I have between 90 -150 names each month in Col F and so I
have to type the service provider name in the each time I filter by Col F.

Is this clearer?



"fam" wrote:

> I have amount due in column G, who owes amount in column H, who provided the
> service in column f.
>
> I need to be able to Filter on column f, total amount due in column G, based
> on who owes in column H.
>
> I wrote a formula which works for totals in column G based on column H, but
> it includes all service providers and I want it to only include the items I
> have filtered for. =SUMIFS(G7:G1448,H7:H1448,1)
>
> Currently Column G subtotals when I filter on column F. I need subtotals
> for column G based on who owes in Column H.
From: Domenic on 3 May 2010 20:55
Try...

=SUMPRODUCT(--(H7:H1448=1),SUBTOTAL(9,OFFSET(G7:G1448,ROW(G7:G1448)-ROW(G7),0,1)))

--
Domenic
Microsoft MVP - Excel
www.xl-central.com, "Your Quick Reference to Excel Solutions"

"fam" <fam(a)discussions.microsoft.com> wrote in message
news:6ABC953E-E580-4B7D-8B6F-CDB87CC511BD(a)microsoft.com...
> I have amount due in column G, who owes amount in column H, who provided
> the
> service in column f.
>
> I need to be able to Filter on column f, total amount due in column G,
> based
> on who owes in column H.
>
> I wrote a formula which works for totals in column G based on column H,
> but
> it includes all service providers and I want it to only include the items
> I
> have filtered for. =SUMIFS(G7:G1448,H7:H1448,1)
>
> Currently Column G subtotals when I filter on column F. I need subtotals
> for column G based on who owes in Column H.

 | 
Pages: 1
Prev: countif/sumproduct + month
Next: Conditional Formatting - stuck on formula