Successive IF to SUM

[Zylock]

Hello! First time posting for help with excel formula. I've so far been successful at finding solutions to my relatively simple problems. This is the first task that I need help with!

What I'm trying to do is:
- Look into a cell to see if the DATE stored there is within a certain range
- THEN look into a different cell to see if the data there is one of a small list of predefined values
- THEN, if the second cell contains the right data, look into another cell to pull a number
- Finally, I want to SUM all of the successful outputs into a final, separate cell.

I'm using a spreadsheet to track pipe inspections. Each pipe is a certain length (in meters), but is only considered complete when there is an inspection done from both ends. I need to tally the total meters of each week's successfully completed pipe inspections. So far I have all of the input columns set up and correctly configured with data validation. The two columns that track if an inspection has been done from each end of the pipe have the preset values "Yes," "Needed," "Impossible," and "See Comments." For the purposes of a complete inspection, only "Yes" and "Impossible" would be considered sufficient for completion. The date column is set up to contain only dates input by the date-selection field. And there is a column containing the total number of meters for the pipe segment.

My inexperienced Excel brain has started to conceive of IF statements here and there, but I cannot even begin to see how to accomplish what I need.

Any and all help is deeply appreciated!

 

[Eric W]

Welcome to the MrExcel forum!

This would be easier with SUMIFS, but I don't believe that existed in Excel 2007. So try this:

Book1
	A	B	C	D	E	F	G	H
1	DATE	Status	Length		Start Date	End Date		Total Meters
2	7/1/2022	Yes	1		7/2/2022	7/13/2022		63
3	7/2/2022	Needed	2
4	7/3/2022	Yes	3
5	7/4/2022	Yes	4
6	7/5/2022	Impossible	5
7	7/6/2022	See Comments	6
8	7/7/2022	Yes	7
9	7/8/2022	Impossible	8
10	7/9/2022	Needed	9
11	7/10/2022	Needed	10
12	7/11/2022	Yes	11
13	7/12/2022	Impossible	12
14	7/13/2022	Yes	13
15	7/14/2022	See Comments	14
16
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(--(A2:A20>=E2),--(A2:A20<=F2),(B2:B20="Yes")+(B2:B20="Impossible"),C2:C20)

Adapt the formula to use your ranges.

 

[Zylock]

Whoa! Thank you. That's much more complex than I imagined, and exactly why I came here! I'll give it a shot.

 

[Zylock]

That worked perfect! How would I add a second column of "Yes," "Impossible," to the formula?
Right, I forgot to mention that I'm hosting this spreadsheet on Google Sheets if that's any help in creating a formula.

 

[Eric W]

How would I add a second column of "Yes," "Impossible," to the formula?

What do you mean? Look at a different column that has "Yes" or "Impossible" in it? Do you need a good value in both, or just one of the columns? I'm guessing that you need to check the inspection for both ends, and they both have to be ok?

Right, I forgot to mention that I'm hosting this spreadsheet on Google Sheets if that's any help in creating a formula.

In the future, please mention this in your first post. Although Excel and Google Sheets share many features, there are many differences too. I don't know Google Sheets, and I'd hate to provide a formula that won't work for you. In fact, we have a special forum called "General Questions & Other Applications" where Google Sheets questions should go.


If my assumption above is correct, then try this:

Book1
	A	B	C	D	E	F	G	H	I
1	DATE	Status A	Status B	Length		Start Date	End Date		Total Meters
2	7/1/2022	Yes	Yes	1		7/2/2022	7/13/2022		52
3	7/2/2022	Needed	Needed	2					52
4	7/3/2022	Yes	Impossible	3
5	7/4/2022	Yes	Yes	4
6	7/5/2022	Impossible	Yes	5
7	7/6/2022	See Comments	See Comments	6
8	7/7/2022	Yes	Yes	7
9	7/8/2022	Impossible	Impossible	8
10	7/9/2022	Needed	Needed	9
11	7/10/2022	Needed	Needed	10
12	7/11/2022	Yes	See Comments	11
13	7/12/2022	Impossible	Impossible	12
14	7/13/2022	Yes	Yes	13
15	7/14/2022	See Comments	See Comments	14
16
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(A2:A20>=F2),--(A2:A20<=G2),((B2:B20="Yes")+(B2:B20="Impossible"))*((C2:C20="Yes")+(C2:C20="Impossible")),D2:D20)
I3	I3	=SUM(SUMIFS(D2:D20,A2:A20,">="&F2,A2:A20,"<="&G2,B2:B20,{"Yes","Impossible"},C2:C20,{"Yes";"Impossible"}))

If the original formula worked for you, then the I2 formula should too. I believe Google Sheets has SUMIFS, so you might try the I3 formula which is a bit shorter, but no guarantees on either one.

 

[Zylock]

What do you mean? Look at a different column that has "Yes" or "Impossible" in it? Do you need a good value in both, or just one of the columns? I'm guessing that you need to check the inspection for both ends, and they both have to be ok?

In the future, please mention this in your first post. Although Excel and Google Sheets share many features, there are many differences too. I don't know Google Sheets, and I'd hate to provide a formula that won't work for you. In fact, we have a special forum called "General Questions & Other Applications" where Google Sheets questions should go.

If the original formula worked for you, then the I2 formula should too. I believe Google Sheets has SUMIFS, so you might try the I3 formula which is a bit shorter, but no guarantees on either one.

Sorry about not mentioning use of Google Sheets. I switch back and forth between the two platforms so often, and find that everything I need to do works in both, that I forget there are meaningful differences.

I tried both formula and the first, I2, worked perfectly! Thank you so much! That solves a huge headache that would have plagued me for the next three years!