Formula help

[YOUNAN]

Hello,

I want to calculate cells when the date is 2013 or earlier I am using the following formula but it says its wrong, can anyone help me with this.

=countif(B34*400+C34*200, "=<2013")

Please advise

Thanks in advance

 

[sheetspread]

The first term has to be a range, e.g. =countif($b$34:$c$34,"=<2013")

 

[snjpverma]

younna, what is 400 and 200 there?

 

[YOUNAN]

this is an excel file for a church, and 400 & 200 are subscriptions, so I want a formula to count how much every family has to pay and cells B34 & C34 and number of family members .

Thanks

 

[jtakw]

Hi,

I'm taking a guess here.....I'm assuming you want the Count of cells for year 2013, since you are using COUNTIF....

If Columns B and C Only contains a Number representing the Year you're trying to Count, then use formula in E34,
If the Columns contain Full Dates, then use the formula in E42.

Excel 2010

	B	C	D	E

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]34[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]9/8/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]3/4/2014[/TD]
[TD="align: right"]5/6/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]4/5/2015[/TD]
[TD="align: right"]4/27/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]6/6/2013[/TD]
[TD="align: right"]9/9/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]5/4/2013[/TD]
[TD="align: right"]7/7/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]5/7/2017[/TD]
[TD="align: right"]3/19/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]12/12/2018[/TD]
[TD="align: right"]4/20/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E34[/TH]
[TD="align: left"]=COUNTIF(B34:C40,2013)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E42[/TH]
[TD="align: left"]=SUMPRODUCT(--(YEAR(B42:C48)=2013))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Let us know if this is not what you're trying to do, and please explain in detail.

Edit: seems you posted an explanation of what you want while I was posting this, I'm sure someone will get back to you.

 

[jtakw]

this is an excel file for a church, and 400 & 200 are subscriptions, so I want a formula to count how much every family has to pay and cells B34 & C34 and number of family members .

Thanks

I think you'll need to post a few lines of sample data, your explanation here doesn't tell us where the Date/Year is.

 

[YOUNAN]

Hello


I need a formula that checks when is the member registered and to count how much he should pay for each year from the day he was registered .
so if you can help me and make some changes to my formula

=countif(B34*400+C34*200, "=<2013")

B34 & C34 cells are hidden and they count how many adults and under 18 .
400 & 200 is the amount for adults and under 18.
=< 2013 will count if member was registered in 2013 or before and would be great if result is 0 if member is registered 2014 or after.
for the rest of years I can adjust the formula.


Thanks in advance.

Younan

 

[jtakw]

You still haven't told us Where the information is for the Year, how is the formula going to know if the member registered before, during, or after 2013, where is the registration Date/information?

 

[YOUNAN]

Hello

Registration date is in cell AI4.

Thanks in advance.

Younan

 

[jtakw]

This works for 1 instance of membership subscription costs, since I don't know if you'll be copying the formula down/across, and I assume by "Registration date", you mean it's an actual Date, not just the Year in AI4:

Excel 2010
	B	C	D	E	F	AI
4						4/14/2012
33
34	2	3		$1,400.00
Sheet14

Cell Formulas
Range	Formula
E34	=IF(YEAR(AI4)<=2013,B34*400+C34*200,0)

E34 formula will give 0 as result if registration occurred after 2013.