Re: Question about a function

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 |LIFE MEMBER: VPEA 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>
.

Quantcast