Using sumifs when one condition is comparing an account number with dashes to a list

[djbehar]

Hello,

I'm trying to create a sumifs formula where one of the conditions is to compare the account number (in the form of xx-xx-xxx) to an acceptable list of accounts on a separate tab. Is this possible? I can't share the data, but here's the formula I'm trying to use:

=SUM(SUMIFS('Data Current'!$D:$D,'Data Current'!$C:$C,Accts!B66:B131, 'Data Current'!$A:$A, "*" & $B3 & "*"))

Data Current'!$C:$C is the account number of the record.
Accts!B66:B131 is the list of accounts that are applicable.

Everything else in the formula seems to work.

Thanks!

[FDibbins]

Hi, welcome to the forum

1. does the account actually have - in it, or is it just formatted to show that?

2. It looks to me like your syntax os a bit out there, too. Syntax should be...
=sumifs(sum-range, criteria-range1, criteria1, criteria-range2, criteria2,....)
criteria1 etc needs to be a single cell ref, not a range..

Another possible way around this would be to add a helper, then use MATCH() to find the matching accounts, and sum based on that result

[DavidBowman]

I think in this case you probably need to separate the matching step from the other SUMIFS conditions. So IF(MATCH(),SUMIFS(),0)

[djbehar]

Thanks for the quick reply!

To answer your questions:

1. The accounts do have dashes in them.
2. That's part of my question. Is it possible to sum the values for all of the accounts in the range? If not, what would be the least painful way to do this?

Also, what do you mean by add a helper? I still have a lot to learn...

[JeteMc]

I would have suggested using SUMPRODUCT except for the inclusion of wildcards.
A helper would be another column, presumably added to the 'Data Current' sheet, that in some manner finds matches between records in column A to "*" & $B3 & "*"
You might then be able to write a SUMPRODUCT formula that uses the helper column.
Something like =SUMPRODUCT(('Data Current'!$D:$D)*('Data Current'!$C:$C=Accts!B66:B131)*('Data Current'!$H:$H=TRUE))
Note that in the above formula 'Data Current'!$H:$H is the helper column.
It would be easier to help if we could see a completely fictionalized sample of data, set up as it is on the 'Data Current' and Accts sheets. If I understand correctly, we probably wouldn't need more than 10 rows of data on the 'Data Current' sheet and 2 rows on the Accts sheet. It would be helpful if you would tell us the expected result so we have something against which to compare our proposed formula/code results.
To upload a sample follow the instructions in the fourth banner at the top of the page.
Let us know if you have any questions.

[FDibbins]

As Jete pointed out, a helper column is just that - a column with a formula in it that helps you ID rows that you wish to include in further calcs.

So for instance, in it's simplest form, you could have a formula that pulls out/ID's only those rows with an Acct number that you select. You then use that as the basis of a sum calc