Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF

[minky81]

I receive a #REF error for the formula I'm trying to work on. I would be really grateful for some insight.

What I’m trying to do:

I am working on a personal budget for home expenses. I would like to sum all the categories for each month so I can see what is the highest expense for each month. So in the formula, I am trying to find x (in this case, 'Groceries') for a given month and then calculate the total.

Computer: MAC
Workbook: Expenses.xlsx
Sheets: Budget / OCT12 / NOV12 / DEC12 etc.

Budget Sheet:

A B C D
28 DAILY LIVING OCT NOV DEC
29 Groceries #REF
30 Dining / Eating Out
31 Pharmacy Supplies
32 Dry Cleaning
33 Salon / Hair
34 Eyewear / Contacts
35 Clothing
36 Education / Lessons


OCT12:

A B C D
58 DATE CATEGORY DESCRIPTION AMOUNT
59 1-Oct-12 Bus / Taxi / MRT Taxi $10.00
60 1-Oct-12 Dry Cleaning Astoria $10.00
61 1-Oct-12 Groceries Jasons $10.00
62 12-Oct-12 Groceries Market Place $50.00
63 12-Oct-12 Pharmacy Supplies Guardian - Toiletries $30.00
64 13-Oct-12 Groceries Marks & Spencer $60.00
65 13-Oct-12 Hardware / DIY Services Home-Fix DIY $10.00
66 16-Oct-12 Dining / Eating Out Starbucks Coffee $20.00
etc.


PLEASE NOTE: The B column (CATEGORY) allows you to select via a dropdown menu. Will this cause the formula not to work? I have indented the list on the dropdown to look like this ' Groceries', where as in the Budget Sheet A29, it shows 'Groceries'. This was so I could group categories and it was be easy on the eye when searching.

The formula I used which caused an error in cell B29 is:

=SUMPRODUCT(SUMIF(INDIRECT("'"&OCT12&"'!B59:B110"),$A29,INDIRECT("'"&OCT12&"'!D59:D110")))

Please could you let me know what the issue is.

Thanks

M

 

[Stan_C]

Welcome to the forum!!

I would put your data in one big spreadsheet rather than 10's of sheets, it would be much easier to create formulas/references...

I created to dynamic Validation list. This means you are able to add onto these columns and they automatically populate the list. These are set to range for the loopup data and might to be changed at some point in time.

These are your named range formulas:

Catergories

=OFFSET(Budget!$A$3,,,COUNTA(Budget!$A$3:$A$100),)

(Sheets)

=OFFSET(Budget!$B$3,,,COUNTA(Budget!$B$3:$B$100),)

Just to show so example:

Budget

*	A	B	C	D	E	F
Categories	Sheets	*	Month	Category	Total
*	*	*	*	*	*
Groceries	OCT12	*	OCT12	Pharmacy Supplies
Dining / Eating Out	NOV12	*	NOV12	Groceries
Pharmacy Supplies	DEC12	*	DEC12	Dining / Eating Out
Dry Cleaning	*	*	OCT12	Groceries
Salon / Hair	*	*	NOV12	Dry Cleaning
Eyewear / Contacts	*	*	DEC12	Dining / Eating Out
Clothing	*	*	OCT12	Groceries
Education / Lessons	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:127.2px;"><col style="width:127.2px;"><col style="width:23.2px;"><col style="width:64px;"><col style="width:134.4px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"] $ * 42.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"] $ * 10.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"] $ * 20.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"] $ * 36.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"] $ * 20.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"] $ * 20.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"] $ * 36.00 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F3 {=IFERROR(SUMPRODUCT(--((INDIRECT("'"&D3&"'!B2:B"&COUNTA(INDIRECT("'"&D3&"'!B2:B1000"))+1)=E3))*INDIRECT("'"&D3&"'!D2:D"&COUNTA(INDIRECT("'"&D3&"'!D2:D1000"))+1)),"No Data!")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Data Validation in Spreadsheet

Cell Allow Datas Input 1 Input 2 D3 List * =Sheets * E3 List * =Categories * <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

I receive a #REF error for the formula I'm trying to work on. I would be really grateful for some insight.

What I’m trying to do:

I am working on a personal budget for home expenses. I would like to sum all the categories for each month so I can see what is the highest expense for each month. So in the formula, I am trying to find x (in this case, 'Groceries') for a given month and then calculate the total.

Computer: MAC
Workbook: Expenses.xlsx
Sheets: Budget / OCT12 / NOV12 / DEC12 etc.

Budget Sheet:

A B C D
28 DAILY LIVING OCT NOV DEC
29 Groceries #REF
30 Dining / Eating Out
31 Pharmacy Supplies
32 Dry Cleaning
33 Salon / Hair
34 Eyewear / Contacts
35 Clothing
36 Education / Lessons


OCT12:

A B C D
58 DATE CATEGORY DESCRIPTION AMOUNT
59 1-Oct-12 Bus / Taxi / MRT Taxi $10.00
60 1-Oct-12 Dry Cleaning Astoria $10.00
61 1-Oct-12 Groceries Jasons $10.00
62 12-Oct-12 Groceries Market Place $50.00
63 12-Oct-12 Pharmacy Supplies Guardian - Toiletries $30.00
64 13-Oct-12 Groceries Marks & Spencer $60.00
65 13-Oct-12 Hardware / DIY Services Home-Fix DIY $10.00
66 16-Oct-12 Dining / Eating Out Starbucks Coffee $20.00
etc.


PLEASE NOTE: The B column (CATEGORY) allows you to select via a dropdown menu. Will this cause the formula not to work? I have indented the list on the dropdown to look like this ' Groceries', where as in the Budget Sheet A29, it shows 'Groceries'. This was so I could group categories and it was be easy on the eye when searching.

The formula I used which caused an error in cell B29 is:

=SUMPRODUCT(SUMIF(INDIRECT("'"&OCT12&"'!B59:B110"),$A29,INDIRECT("'"&OCT12&"'!D59:D110")))

Please could you let me know what the issue is.

Thanks

M

Without those leading space chars, you could just have:

=SUMIF(OCT12!B59:B110,$A29,OCT12!D59:D110)

Otherwise, you need:

=SUMPRODUCT((TRIM(OCT12!B59:B110)=$A29)+0,OCT12!D59:D110)

Note. As is, there is no reason for using INDIRECT.

 

[minky81]

Hi all,

Just to inform you, I have not been following the forum rules. I cross posted which I've just been told is not advised. Below is a link to my question on Excel Novice:

Excel Novice - attempting to use SUMPRODUCT/SUMIF/INDIRECT but cell shows #REF.

Before you answer, please could you check this link to see whether I have received the answer you were hoping to post. As soon as my question has been resolved, I will close both threads.

Apologies for any inconvenience.

Thank you

M

 

[minky81]

Thank you Aladin Akyurek, this worked.

Without those leading space chars, you could just have:

=SUMIF(OCT12!B59:B110,$A29,OCT12!D59:D110)

Otherwise, you need:

=SUMPRODUCT((TRIM(OCT12!B59:B110)=$A29)+0,OCT12!D59:D110)

Note. As is, there is no reason for using INDIRECT.

 

[Aladin Akyurek]

Thank you Aladin Akyurek, this worked.

You are welcome. Thanks for providing feedback.

 

[minky81]

I have change the status of Excel Novice to resolved via 'Thread Tools' but I don't have an option to do so with this thread. How would I close this thread?

 

[Aladin Akyurek]

I have change the status of Excel Novice to resolved via 'Thread Tools' but I don't have an option to do so with this thread. How would I close this thread?

Thread Tools does not have an option. Also, there is no need for marking a thread as "resolved" or "solved".