Question about a function

[Phillip Jones, CET]

Works just fine thanks for all the help. between your formulas and mine.
I have more than enough info.

Thanks. I'll Retype it and see what Happens.

Philip,

The way your table is currently set up does not really accomodate the
fact that the Tidewater dues are paid in two instalments. The
following formula will give you a total of how much has been paid,
assuming all Tidewater with a "y" paid status have only paid half that
amount:

=SUMPRODUCT((A2:A45="Tidewater")*(D245="y")*(C2:C45))/2+
SUMPRODUCT((A2:A45="At-Large")*(D245="y")*(C2:C45))+
SUMPRODUCT((E2:E45="2years Paid")*(C2:C45))

Subtracting the result of this from SUM (C2:C45) wouldn't give you the
amount to be paid though as the payments include a year's overpayment
(add on this $24 to give you the true dues still owing). This is what
I meant in an earlier post about overpayments needing to be accounted
for separately.

A)Total Dues =SUM(C2:C45)

B)Payments made
=SUMPRODUCT((A2:A45="Tidewater")*(D245="y")*(C2:C45))/2+
SUMPRODUCT((A2:A45="At-Large")*(D245="y")*(C2:C45))

C)OverPayments =SUMPRODUCT((E2:E45="2years Paid")*(C2:C45))

Amount still owing would be formula B subtracted from formula A.

HTH

Jay

See If I can explain everything.
(I'm still saving your and my Comments.)

I am Treasurer of an Electronics Association.

At-Large members Meaning people all over the state and Country pay
Dues One time a Year.

They are supposed to pay within 30 days of dues invoice around the
first of July. After the 30 day period, they are given a grace period
of another 30 days but they no long can participate in activities nor
vote until paid. If at the end of this Grace period they don't pay
they are dropped altogether.

We also have have affiliate Associations. (Locals). They collect
local dues, plus State dues for each member. Because they are an
Affiliate and have their on By-laws; if their By-laws state they can
pay in two Parts July and Jan.; the state must go along.

So in July I receive full payment from all the "At-large members" and
1/2 payment from those at Tidewater.

WE have different categories of membership. This applies to At-Large
or Affiliates

Full membership is $48.00
Associate Business, technician, Industry, Electronics Instructor,
Retired, at: $24.00.

Associate Partner is $12.00
Life is $0.00

Becoming Retired has nothing to do with, becomeing a Life Member.
That's voted on by the board of Directors as an honor to that person
in consideration for their service to the Association. Life members
can voted in as board members or Officers.

So the amounts in the Dues Column if Totaled up, would be a Grand
Total of what we should get this year.

We had this one person to pay 2 years worth of Dues. And there has
been precedent set where members have paid for two years in the past.
(The next year we didn't collect any from that person because they
already paid.)

What I am trying to do is get a grand total of all Dues to be paid.
And account some how for the extra money paid, and the money not
received until Jan. At which point It should either be zero or Plus
what ever 2 year payments would be.

Since this is my 16th year and hopefully my last if it comes to zero
in Jan will be fine.

Jay wrote:
Philip,

Regarding your observation that the SUM(C2:C50) didn't include those
paying for 2 years. This was done purposefully as I wasn't aware
that any 'overpayments' (of an extra year) were to be classed as
actually "dues" (with them being in fact paid prior to becoming due).

If you wish to class them as 'dues' rather than accounting for them
elsewhere then your formula would give the result you're after.

Although, you have a criteria of a single space text string in your
additional SUMIF [ SUMIF(D250," ",C2:C50) ]. This will not work
if in fact the cells are empty, as they are in the .xls file you
uploaded. This would need changing to an empty text string,
indicated by double inverted commas "".

And, actually the first two SUMIFs wouldn't even be required. It
could be simplified to:

=SUM(C2:C45)+SUMIF(E2:E45,"2years Paid",C2:C45)

I've adjusted the ranges as mentioned in your last post.

Regarding the additional formula re Tidewater, I am unsure what you
meant by "anything with Tidewater only is figured", so can't really
advise as to the formula. If you want to post additional
explanations I'd be glad to have a look.

Regards

Jason


Phillip Jones wrote:
Your first formula

=SUM(C2:C50) doesn't take into consideration people that pay for
two Years.

So didn't my addition of the Cells by adding the part:

+ SUMIF(D250," ",C2:C50)
(which would be sum of the empty Cells added to your formula)
wouldn't that Make a Grand Total of all Dues?

As for the difference. My apologies. after I wrote the formula and
saved, if you notice on the one you looked at there are several
that pay zero. So their amounts would make no difference. So I
deleted their lines out, which made the difference between C2:45
and C2:50. Excel has a great feature when a row is deleted that
affects a SUM or AUTOSUM it compensates in the formula. In my
Original Database/Spreadsheet I have left them in and so your
figures are correct.

I've also had to create another formula which uses the Column
designated for Membership. So that anything with Tidewater only is
figured then I divide that in half SUMIF(Formula)/2 .

That is subtracted from my original dues received. This is the true
amount Collected during this "half Year". When The Local pays the
other Half of their dues in Jan then this is removed. When this is
removed. if everyone pays, amount not paid will end up zero.

Please advise if I am correct. I am saving these to go over just in
case

Jay wrote:
Hi Philip,

Glad it worked. Yes the amounts given by the formula will change
when you mark other people as paid. (That's assuming you have
Excel to calculate automatically which I'm almost certain you will
have).

Regarding your amendment. I see you've added another SUMIF to add
all those who haven't paid. I would point out however that you
haven't used the same ranges - you've used D245 and C2:C45 when
this should be D250 and C2:C50. It's the same range that you are
checking so the range should be the same as the other SUMIFs.

Although you don't really need a SUMIF. A straightforward SUM
would give you the total dues:

=SUM(C2:C50)

And there's a problem in subtracting your two formulae to get the
amount to collect as this doesn't take into account the people who
have paid 2 years dues. Try these:

Total Dues =SUM(C2:C50)

Total Paid =SUMIF(D250,"y",C2:C50)+SUMIF(E2:E50,"2years
Paid",C2:C50)

Dues Owing =SUM(C2:C50)-SUMIF(D250,"y",C2:C50)

HTH

Jason


Phillip Jones wrote:
Tried your formula. Works great!

I even altered it for a total Dues and then subtracted the two
for amount left to Collect.

My alteration was asfollows:


=SUMIF(D250,"y",C2:C50)+SUMIF(D245,"
",C2:C45)+SUMIF(E2:E50,"2years Paid",C2:C50)


The only thing both on your formula and my alteration there is a
note about reference to Blank cells.

I can deal wit that though.
Jay wrote:
Hi Philip,

Based on the .xls file, the following formula will give you a
total dues paid (including double payment for any 2years paid)

=SUMIF(D250,"y",C2:C50)+SUMIF(E2:E50,"2years Paid",C2:C50)

This formula relies on the input of "y" and "2years Paid" for
the calculation. So, for example "2 years paid" wouldn't count
(as there's an extra space in the text string.)

Extend the ranges, accordingly to accommodate any more data,
although bear in mind that the two ranges within a SUMIF (the
criteria range & range to be summed) must be the same size (in
this instance rows 2 to 50 in both cases).


HTH

Jason




Phillip Jones wrote:
See this link:

<http://www.kimbanet.com/~pjones/membersPaida.xls>

Phillip Jones wrote:
He has paid the equivalent of two years dues. in dues column
shows one years dues amount. the next column beside indicates
Status y (yes) or blank (no) the next year years paid.

if you wish I can Up load to website and post the URL.

Jay wrote:
Philip, your explanation hasn't really answered my question.

If someone has paid 2 years ('2 years paid' in the Paid
column) does this mean that:

A) the amount in the Dues column is equal to 2 years dues? OR:

B) Is the amount actually one year's dues, meaning s/he has
paid double that amount?

Scenarios A and B need different formula so it would need
clarifying first.


Jason



Phillip Jones wrote:
currently I have one person that's paid 2 year's worth of
Dues. He is an Associate member at $24.00 (US)

Associate Business membership is $24.00 (US) per year
Full membership is $48.00 (US) per year

(one year is represented in this particular spreadsheet by
the y)
We have other categories of Associate Membership that all
are $24.00 except for Partner which mean a Partner in a
business. That rate is $12.00 for that one.

We have life members that pay no dues. $00.00 so they need
not be in the spreadsheet/database.

In one case this year we had one person to pay Associate
membership twice and decided to just leave it and consider
it a tow year membership.

We have a precedent in the past where a members has paid two
years worth of dues.

I want to keep a running total of the dues collected so that
when amount is paid its added then I can subtract from the
amount I should collect. to show how far behind we are in
dues collection.

I suppose I could alter the spreadsheet/database and leave
off the names for privacy and post some where for everyone
to look at. I don't think its all that complicated, just I
can't figure out how to get started.

I normally use Filemaker and it has functions. I have set up
some for dues total there but very complex.

For this I don't need anything that complex.

Jay wrote:
JE McGimpsey wrote:
I am a frequent user of Excel, But haven't used Functions
in Excel.

Question in a spreadsheet/database I have one column Dues
being of different amounts dependent upon membership.
Then I have a column named paid.

the information in the paid column is either

y for yes
2year paid two years paid
blank for unpaid.

How would I word a Function to total all the dues that
are paid taking into consideration the multiple year
payment.

One way:

Assume status in column B, amount in column C:

=SUM(C:C)-SUMIF(B:B,"2year paid",C:C)/2

John,

I'm not sure your formula takes into account the blank
cells in the paid column which represent unpaid amounts.
Wouldn't these need excluding from the sum?

Philip,

For the '2year paid items', does the amount in the previous
'Dues' column represent one year's subscription, or two
years. So if there was the following:

Dues Paid
-------------------
£20.00 2years paid

Does this mean the member had paid 2years membership (£10
per year x 2)?

Or would it mean that the membership was £20 per year, and
the member had paid 2 years, therefore paid £40.

If you could clarify this I'd be happy to provide you with
a formula to do what you need.

Jason

--
------------------------------------------------------------------------
Phillip M. Jones, CET |MEMBER:VPEA (LIFE) ETA-I, NESDA,ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>