IF AND array

[ellisbobby]

Hello all,

I am trying to create an array to where if two rows contain certain text, it will SUM the totals of the corresponding cells. The current array I am using is below ("Data" is the name of the worksheet that it pulls the information off of). So if the information in row 4 equals cell A4, it will SUM the correct cells in row 37.

{=SUM(IF(Data!$4:$4=$A4,Data!$37:$37))}

I'm trying to get the array to only sum if the data in row 4 equals cell A4 and the data in row 3 equals A3 are true. I tried the array below but it doesn't work.

{=SUM(IF(Data!$3:$3=$A3,Data!$4:$4=$A4,Data!$37:$37))}

Does anyone know if this is possible? Thanks in advance.

 

[ExcelElliott]

Try the following:

{=SUM(IF(Data!$3:$3=$A3,IF(Data!$4:$4=$A4,Data!$37:$37)))}

Another option is SUMPRODUCT:

=SUMPRODUCT((Data!$3:$3=$a3)*(Data!$4:$4 =$a4)*(Data!$37:$37))

 

[ellisbobby]

ExcelElliott... Thank you so much! You just saved me a ton of time! I went with the first formula, but I'll give the second a shot as well. I can't thank you enough!

 

[ExcelElliott]

You are welcome, glad I could help.