Sum a range if range of cells contains a specific word in a text string

[wakefield101]

Hi I am trying to figure out what appeared to be a simple formula however I thought wrong.

Does anyone know how to have a SUMIF apply if a cell contains a certain text?

Here is the formula that I have now, which is not working... =SUMIF(AJ1:BC1,"=*Reserved*",OR("=*Actuals*"),AJ2:BC2)

What I want the formula to say is, if the range of cells have the word "Reserved" in it, then sum all corresponding rows, if not than sum the range with the word "Actuals" in the cell

Here is an example of the data I am using.

Excel Workbook
	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1	FEB-2016 Reserved	FEB-2016 Actuals	MAR-2016 Reserved	MAR-2016 Actuals	APR-2016 Reserved	APR-2016 Actuals	MAY-2016 Reserved	MAY-2016 Actuals	JUN-2016 Reserved	JUL-2016 Reserved
2	0	0	0	0	0	0.24	0	0	0	0
3	0	0	0	0	0.24	0	1	0	1	1
4	0	0	0.09	0	0.18	0	0.18	0	0.18	0.18
5	0	0	0	0	0	0	0	0	0.84	0.88
6	0	0	0	0	0	0	0	0	0.46	0.84
Data

If thee is a another way then the sumif formula, please let me know.

 

[63falcondude]

Have you tried using =isnumber(search()) in place of the wildcard *

​https://exceljet.net/formula/cell-contains-specific-text

Also, check your use of the or function. Should be of the form OR(logic1,logic2,...)

 

[wakefield101]

I just tried this formula:

=SUM(IF(ISNUMBER(SEARCH("Actuals",AJ1:BC1)),AJ2:BC2,IF(ISNUMBER(SEARCH("Reserved",AJ1:BC1)),AJ2:BC2,"ERROR")))

and

=SUM(IF(ISNUMBER(SEARCH("*Actuals*",AJ1:BC1)),AJ2:BC2,IF(ISNUMBER(SEARCH("*Reserved*",AJ1:BC1)),AJ2:BC2,"ERROR")))


However, I received a #value error. Excel Formula Evaluation says that it can't determine values for AJ1:BC1 (which are my headers)...

 

[63falcondude]

I am confused as to what you are trying to take the sum of. Do you want the sum of all of the columns where the header says Reserved and then another sum of all of the columns where the header says Actuals? Or do you want the individual row sums where the title is Reserved etc.

If you can make a very small sample data that shows all of the columns and rows I think it might help here.

 

[jtakw]

Hi,

Shouldn't you have the Totals separated instead of in the same Column?, Here're both ways:

Excel 2010
	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1	FEB-2016 Actuals	MAR-2016 Reserved	MAR-2016 Actuals	APR-2016 Reserved	APR-2016 Actuals	MAY-2016 Reserved	MAY-2016 Actuals	JUN-2016 Reserved	JUL-2016 Reserved	Total Reserved	Total Actuals	Mixed Total
2	0	0	0	0	0.24	0	0	0	0	0	0.24	0.24
3	0	0	0	0.24	0	1	0	1	1	3.24	0	3.24
4	0	0.09	0	0.18	0	0.18	0	0.18	0.18	0.81	0	0.81
5	0	0	0	0	0	0	0	0.84	0.88	1.72	0	1.72
6	0	0	0	0	0	0	0	0.46	0.84	1.3	0	1.3
Sheet1

Cell Formulas
Range	Formula
AT2	=SUMIF(AJ$1:AS$1,"*Reserved*",AJ2:AS2)
AU2	=SUMIF(AJ$1:AS$1,"*Actuals*",AJ2:AS2)
AV2	=IF(SUMIF(AJ$1:AS$1,"*Reserved*",AJ2:AS2)=0,SUMIF(AJ$1:AS$1,"*Actuals*",AJ2:AS2),SUMIF(AJ$1:AS$1,"*Reserved*",AJ2:AS2))

Formulas copied down.

 

[wakefield101]

WOW, as I was typing, I realized that I was trying to work the wrong formula. Stupid...

I just realized that I had the formula that I needed about 2 hours ago....

Sorry for the confusion and thanks for the help..

 

[jtakw]

You're welcome, 2 hours ago was when you posted your question.

 

[wakefield101]

Yes exactly Jtakw. haha wow and the formulas that you wrote in the above thread were the exact formulas that I figured out this morning (its 13.30 right now)......

For some stupid reason, I had it in my head to combine the formulas, little did I think that what I was trying to do was contradicting... Time to step away for 15 minutes and clear my head hahaha