SUMIF INDIRECT returning 0

[hulio82]

Hi everyone, longtime excel user and mrexcel lurker, first time poster. I'm running into some issues which probably are just due to my lack of excel knowledge.

Essentially, here is what i'm trying to do (this is an example,but i'm trying to do this for larger amounts of data):

Sheet 1:
Row 1 Row 2 Row 3
A X $1
B Y $2
B Z $3
C Q $4

Sheet 2:
Result-
A total: $1
B total: $5
C total: $4

I want to be able to generate the results on a different sheet. I tried using Sumif paired with indirect and keep on getting 0. It appears to work fine when i use the formula on Sheet 1, but when i try to use the formula on sheet 2, it returns 0.

any help is appreciated. if needed, i can upload my actual spreadsheet. in a hurry now, so just put in an example.

 

[mvptomlinson]

Not sure why you're using INDIRECT?? Just use SUMIF

=SUMIF('Sheet1'!$A:$A,"A",'Sheet1'!$C:$C)

This will sum column C of Sheet1 where column A contains the letter "A".

 

[hulio82]

just saw my post and realized it is incomplete. here is the updated question with more details.

Essentially, here is what i'm trying to do (this is an example,but i'm trying to do this for larger amounts of data):

Sheet 1:
( ) (A) (B) (C)
(1) A X $1
(2) B Y $2
(3) B Z $3
(4) C Q $4

Sheet 2:
Result-
A total: $1
B total: $5
C total: $4

I want to be able to generate the results on a different sheet. Since i'm dealing with large amounts of data which changes frequently, it becomes burdensome having to go back and change the ranges. e.g., if using Sumif(A1:A4,"B",C1:C4) which results 5. If i later add in additional data, i have to manually go in an update the ranges. So, i tried to simplify it by listing the ranges in a seperate table.

Sheet1:
Ranges-
( ) (A) (B)
1 A1:A1 C1:C1
2 A2:A3 C2:C3
3 A4:A4 C4:C4

So whenever data moved around and i had to make updates, i could just update the range in one location and it would update my formulas automatically. To do this, based on my research, i had to use the indirect function. So my function was something like Sumif(Indirect(Sheet1!B1),"B",Indirect(Sheet!B2)) which should ideally return 5, but instead its returning 0.

any help is appreciated. if needed, i can upload my actual spreadsheet. in a hurry now, so just put in an example.

 

[mvptomlinson]

Why don't you use my example?

You don't have to use a specific range of cells, you can reference the entire column.

Instead of

=SUMIF(A1:A5,"A",C1:C5)

use

=SUMIF(Sheetname!A:A,"A",Sheetname!C:C)

Or am I missing something??

 

[Asala42]

Hulio82, I see a lot of problems in your example, but the main thing here is I don't think you need an indirect at all.

Instead of the hardcoding the critieria as in (in Sheet2 B2):
=SUMIF(Sheet1!$A$1:$A$4,"A",Sheet1!$C$1:$C$4)

couldn't you use:
=SUMIF(Sheet1!$A$1:$A$4,A2,Sheet1!$C$1:$C$4)

Where Col A contains just your criteria list.

A variant, if your Sheet2 Column A list all take the form "name Totals"
=SUMIF(Sheet1!$A$1:$A$4,SUBSTITUTE(A2," Totals",""),Sheet1!$C$1:$C$4)