if statement for checking dates

[zoog25]

Hello All,

Here is my situation that I want to see if possible. I have a sheet that contains three columns related to physical project areas. Column 1 contains a date of when the project comes to us, column 2 contains how the project has been divided (Ii.e. 5 lots, 6 lots, etc.) so it will contain only a number, column 3 will have the size of the project (i.e. lot 1 is 12 Acres), the entry just is say the number 12.

Anyways in Cell E2, E3, E4, E5, they want us to put information of all the projects divided into quarter reports, so E2 is labeled 1st Quarter, etc. So i was wondering if there was a way to just add an If statement that would look through all of Column 1, it the date in the cell matches the quarter, it would take the entry in column 2 and add them all together. So lets say A5, A7, A19 area all within the 1st Quarter of the Year (January to March) it would then add up Cells B5, B7, and B19 into E2, so if B5 = 10, B7 = 3, & B19 = 7, then E2 = 20. Please let me know if this is possible using just an IF State. I know i could properly do something with VBA but the person requesting this just like VBA formulas in a project so i just want to see if it's possible using formulas instead. Thank you for any assistance that you can provide. Please also let me know if this is impossible and that way i can see about an alternative method.

 

[SQUIDD]

Hi

So you could check for the month numbers?

the below checks for jan,feb and march(1,2,3)

=IF(OR(MONTH(a1)=1,MONTH(a1)=2,MONTH(a1)=3),"yes","")

Dave

 

[zoog25]

Thank you for the information. I'll be able to use it for my project.

 

[SQUIDD]

ok great.

if you need any help with the actual formula, just ask

dave

 

[zoog25]

i did have a question, i know the formula is just for 1 cell at a time, is it possible to do a range, say like A1 to A10 for example.

 

[SQUIDD]

Difficult to answer exactly.

But would you mean using something like sumif()

summing the range that matches the criteria?

Dave

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this what you are after?

21 08 02.xlsm
	A	B	C	D	E
1	Date	Lots	Size	Qtr	Total Lots
2	15-Jan-21	5	10	1	15
3	28-May-21	2	8	2	6
4	31-Jul-21	3	5	3	8
5	02-Aug-21	5	10	4	0
6	01-Apr-21	4	10
7	16-Feb-21	6	12
8	16-Feb-21	4	11
9
Sum Quarters

Cell Formulas
Range	Formula
E2:E5	E2	=SUMPRODUCT(--(MONTH(A$2:A$8)<=3*D2),B$2:B$8)-SUM(E$1:E1)

 

[zoog25]

Yes that is what I'm looking for more specifically. I'll check on my profile to make sure that I make sure the right version is listed. The problem is that I'm sort of using two different versions of excel. The one on my personal computer and the one at work. I'll probably try to make it so it matches my work computer.

 

[Peter_SSs]

I'll probably try to make it so it matches my work computer.

The account settings allow you to show multiple versions in your profile.

 

[zoog25]

Thanks for the info Peter_SSs. I'll review which version I'm using so I can update my information.