Use SUMIF and INDIRECT

[k_gitnos]

Hi,

At the moment the following line works fine

=SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$3"),$C4:$C28)+SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$8"),$C4:$C28)

Assuming INDIRECT("[File1.xlsx]Sheet1!$A$3") = Apple and INDIRECT("[File1.xlsx]Sheet1!$A$8") = Orange

Instead of writing the following code

=SUM(SUMIF($A4:$A28,{"Apple","Orange"},$C4:$C28))

whereas I'm going to be flexible any more,

I want to use either INDIRECT or another function which will bring in the two cells from the other file into my SUMIF function.

 

[Special-K99]

I've read this four times now and I dont understand what you're after.

First, this

"whereas I'm going to be flexible any more,"
doesnt make sense.
Is this a typo? Do you mean

"whereas I'm not going to be flexible any more,"

"I want to use either INDIRECT or another function which will bring in the two cells from the other file into my SUMIF function. "

You're already using INDIRECT to bring in the two cells.
Can you explain what you're trying to do in another way, or provide some examples?

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[k_gitnos]

Indeed, I made a mistake. I meant "whereas I'm not going..."

Basically, what I want to do is to avoid having the same thing twice in a cell just by changing a parameter. If you notice the difference between

SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$3"),$C4:$C28)

and

SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$8"),$C4:$C28)

is the part in bold.

Instead of summing up these two lines separately, I want to have them under a single function.

Apologies if my English is not that well.

If I'm not clear enough for you, please tell me to try explain it even further.

 

[Special-K99]

Does this work (untested)?
Similar to what you had.

=SUM(SUMIF($A4:$A28,INDIRECT({"[File1.xlsx]Sheet1!$A$3","[File1.xlsx]Sheet1!$A$8"}),$C4:$C28)

 

[Special-K99]

or even this?

=SUM(SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$"&{"3","8"})),$C4:$C28)

 

[k_gitnos]

Unfortunately not. It doesn't work. It gives me 0 as a result.

When I evaluate the formula it shows #VALUE! where the INDIRECT function is.

Please allow me to repeat what I look for.

I have a function
SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$3"),$C4:$C28) whereas INDIRECT("[File1.xlsx]Sheet1!$A$3") is "Orange"

This means if I don't use INDIRECT function, the SUMIF function goes like this SUMIF($A4:$A28,"Orange",$C4:$C28)

The same for the second part

SUMIF($A4:$A28,INDIRECT("[File1.xlsx]Sheet1!$A$8"),$C4:$C28) whereas INDIRECT("[File1.xlsx]Sheet1!$A$8") is "Apple"

This means if I don't use INDIRECT function, the SUMIF function goes like this SUMIF($A4:$A28,"Apple",$C4:$C28)

If I combine these two functions together it is going to be

SUM(SUMIF($A4:$A28,{"Orange","Apple"},$C4:$C28) and it works,

but I don't want to use text in there. I want to have a function and not plain text.

 

[Special-K99]

Try basing something on this.

Set up blank worksheets Sheet 1 and Sheet2

in Sheet2!A1 put 3
in Sheet2!A3 put 4

in Sheet1!A1
=SUM(SUM(INDIRECT("Sheet2!A"&{"1","3"})))

This produces 7
You should be able to change the second SUM to SUMIF with search criteria
and the INDIRECT to your workbook location replacing {"1","3"} with {"3","8"}

 

[k_gitnos]

My mistake that I didn't bring it up earlier.

In the range A4:A28 I don't have only "Orange" and "Apple". I have other values as well.

I try to tell it to look for "Orange" and "Apple" and sum up the values from the respective row from column C.

If this becomes too complicated, please don't bother any more. I really appreciate the time you have spent to help me.