Re: Question about a function

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

Phillip Jones wrote:
Thanks. I'll Retype it and see what Happens.

Jay wrote:
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")*(D2:D45="y")*(C2:C45))/2+
SUMPRODUCT((A2:A45="At-Large")*(D2:D45="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")*(D2:D45="y")*(C2:C45))/2+
SUMPRODUCT((A2:A45="At-Large")*(D2:D45="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(D2:D50," ",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(D2:D50," ",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/Spread*** 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 D2:D45 and C2:C45 when this should be D2:D50 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(D2:D50,"y",C2:C50)+SUMIF(E2:E50,"2years Paid",C2:C50)

Dues Owing =SUM(C2:C50)-SUMIF(D2:D50,"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(D2:D50,"y",C2:C50)+SUMIF(D2:D45," ",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(D2:D50,"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 spread*** 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 spread***/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 spread***/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:
In article <#1p4jBYvGHA.5044@xxxxxxxxxxxxxxxxxxxx>,
Phillip Jones <pjones1@xxxxxxxxxxxx> wrote:

I am a frequent user of Excel, But haven't used Functions in Excel.

Question in a spread***/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 |pjones@xxxxxxxxxxxx, ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

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

mailto:pjones@xxxxxxxxxxxx

<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>
.