Cross-Sheet Formulas

Hi! I am trying to reference another sheet in a SUMIF. But I get "#UNPARSEABLE"

I want to sum contribution $ in Column 2 if the vendor name is 9X. This info is in Sheet2. I am referencing it in Sheet1

Here is the formula : =SUMIF({VEND},"=9X",[{DSU}])

I have tried the formula =SUMIF({VEND},"9X",[{DSU}])

And as: =SUMIF([{VEND}1:{VEND}200],"9X",[{DSU}1:{DSU}200])

What am I doing wrong?

Find more posts tagged with

Sales

Sheets

Formulas

Marketing

Comments

L_123

=SUMIF({VEND},"9X",{DSU})

should work, but not being able to see what the range references are I can't be positive it will work

Try that and see if it works, if it doesn't, then please explain how you made the range references.

Swathi.Chaturvedi

Well, it changed from "UNPARSEABLE" to "INVALID REF". Could it be because the first reference range is not numerical?

L_123

the first reference range doesn't need to be a number. only the second. If you have a text value inside of the dollar range that could mess it up, but it seems more likely to me that the ranges aren't matching up.

The references need to contain the same amount of criteria. For example it will give you an error if you have 10 rows referenced in one and 11 in the other.

the easy way to do that is to use column references by clicking on the header of the column. If that isn't a viable solution for you you simply need to make sure the references have the same amount of data.

Can you attach a screenshot of the sheet that is being analyzed?

Swathi.Chaturvedi

Wow! Thanks! I had a few values empty in the sum_range. It worked!!!

L_123

Glad to help

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…