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
50," ",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
50," ",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 D2
45 and C2:C45 when
this should be D2
50 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
50,"y",C2:C50)+SUMIF(E2:E50,"2years
Paid",C2:C50)
Dues Owing =SUM(C2:C50)-SUMIF(D2
50,"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
50,"y",C2:C50)+SUMIF(D2
45,"
",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
50,"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
