sumifs not working? Is there a better way?

[pshearer]

Hey all. See the attached sample. I'm wanting: if the number in col I matches the number in column D, look in col B for the word "house" and sum those amounts in E.

I thought a sumifs would work but its producing 0. Seems like there should be a solution but I'm not sure if it's possible or not.

The actual data is 1200 rows long and with inconsistent placement of the values in column D and B. Ideally would use full columns in formula, ie. B:B, D:D.

[GlennUK]

Why don't you fill down into the blanks in column D, as then it would work? Select D1:D19, Goto[i.e. F5]/Special/Blanks and then type = then UpArrow then Ctrl+Enter

[TMS]

SUMIFS would expect both conditions to be met on the same row. I don't think that what you want is possible with an Excel function/formula.

[AliGW]

Is this what you mean?

=IF(D2=I2,SUMIF($B$4:$B$10,"House",$E$4:$E$10),0)

[TMS]

@Ali: is that going to scale up? Thought you might be offering Power Query

[pshearer]

Is this what you mean?

=IF(D2=I2,SUMIF($B$4:$B$10,"House",$E$4:$E$10),0)

Thanks Ali, but yeah, the problem is that that would not scale. The actual data is about 1200 rows and the placement of the number and "house" is not uniform in the data.

[bebo021999]

It is better to extract the ID number into another helper column, column F, by:

F2:

Please Login or Register  to view this content.

Drag down

Then SUMIFS in J2:

Please Login or Register  to view this content.

Note: B18 was "House " (with a trailing space), delete it.

[AliGW]

@Ali: is that going to scale up? Thought you might be offering Power Query

If I had a real clue what was going on I might ...

[AliGW]

Had another look. Here's a PQ solution. you can scale this by selecting a larger area to upload in the first instance.

M Code:

Please Login or Register  to view this content.

[Glenn Kennedy]

Another way... no helper... but unless you DO have 1,000,000 rows, do not go mad and use whole column references. How many rows do you have, at a maximum?

=SUMPRODUCT(--(LOOKUP(ROW($D$2:$D$1000),ROW($D$2:$D$1000)/($D$2:$D$1000>0),$D$2:$D$1000)=I2),--($B$2:$B$1000="House"),$E$2:$E$1000)

[BMV]

long but non array and quick i spouse

Please Login or Register  to view this content.

[pshearer]

Guys; I really appreciate these responses; you don't even know. The time it's going to save me, the work on my plate, etc. It's 5:00am where I am and I'll be testing these out today. I tested Bebo solution and it works. I'll be testing the others as well and giving appropriate credit and may reach out with some future work if some of you are available. Thanks again guys.

[pshearer]

Bebo; this solution works. Amazing.

[pshearer]

Another way... no helper... but unless you DO have 1,000,000 rows, do not go mad and use whole column references. How many rows do you have, at a maximum?

=SUMPRODUCT(--(LOOKUP(ROW($D$2:$D$1000),ROW($D$2:$D$1000)/($D$2:$D$1000>0),$D$2:$D$1000)=I2),--($B$2:$B$1000="House"),$E$2:$E$1000)

I will try this and respond. Thank you so much for this additional suggestion.

[AliGW]

Any feedback on the solutions Glenn and I offered?

[pshearer]

Any feedback on the solutions Glenn and I offered?

Glenn's works. Ali; I don't know PQ...I don't know how to test that. I'll +1 the hell out of both of you for the answers you gave. Can't thank you guys enough for the help.

[AliGW]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.