SUMIFS returns 0 using dynamic criteria, with crit

问题:

Anyone,

I've chatted with and called excel customer service with no luck. I used the formula builder (please see attached screenshot) to make sure each element of the formula is correct and returns the value for the criteria I'm trying to reference.

Everything is accurate, but it returns a value of 0. When I do the same thing in the actual sheet the data is stored in (and click a criteria cell within the criteria range) it returns the accurate value?! I'm not sure why it won't work on the other sheet. The values I am using to select are dynamic and change with a drop down. I have another, advanced, workbook (I did not create) that does the same thing and completes an even more complicated formula, but actually works so I'm not sure why this is returning a 0 value.

Photos and code/syntax: Dynamic Selection, Example 2 of it working, Example 1 of it working, Formula Builder, CountIFs, Advanced Spreadsheet working, VLOOKUP

=SUMIFS('GFEBS Pull'!Q:Q,'GFEBS Pull'!G:G,FMCOP!$C$20,'GFEBS Pull'!H:H,FMCOP!B23)

or:

=SUMIFS('GFEBS Pull'!Q:Q,'GFEBS Pull'!G:G,'FMCOP'!$C$20,'GFEBS Pull'!H:H,'FMCOP'!B23)

When I type ' around FMCOP sheet name, they disappear? I've also tried to lock the columns on the 'GFEBS Pull' sheet with no luck. Cell B23 is not locked because I'm going to copy the formula down to reference other cells. Any help is appreciated!

回答1:

In this screenshot you can clearly see that both FMCOP!C20 ansd FMCOP!B23 have prefacing spaces; e.g. " HHC".

Since " HHC" will never match "HHC", fix the data returned from 'the lower table in the same screenshot'.

A Text-to-Columns, Fixed Width, Finish should do this. You could adjust the original formula like,

=SUMIFS('GFEBS Pull'!Q:Q, 'GFEBS Pull'!G:G, TRIM(FMCOP!$C$20), 'GFEBS Pull'!H:H, TRIM(FMCOP!B23))

I would caution against the latter 'bandaid' fix. Fix the original data; do not apply bandaids on-the-fly.