Repetitive SUMIFS that don't work with new cells

[Jp4Real]

Hi Guys,

Here is my problem, I'll try to be as brief and clear as possible

Basically I have a formula for Cells on page 2 : =SUMIFS(SLING;type;C10)

SLING refers to page 1 AC:14:AC10000

I won't mention the others because they don't matter they won't change


I'm tryin to add the same formula to a cell next to the first one, it would be basically the same as the other formula but i would modify SLING for EXTRA1 where EXTRA1 refers to AD14:AD10000
EX =SUMIFS(EXTRA1;type;C10)

my problem is when i do that the first formula (SLING) works perfectly BUT the new one (EXTRA1) does not work at all, (It shows value) although I look in the formula builder wich cells it is refering to and the cells are correctly showed.

I look in the cells format to make sure everything was NUMBERS and nothing was text only or date only or whatever

I also tried to modify the EXTRA1 to PAGE1!AC14:AC10000 but no improvement

If anyone could help it would be greatly appreciated !

Thanks !!

[Jonmo1]

Are there any #VALUE! errors within range AD14:AD10000 ?

[XOR LX]

"I won't mention the others because they don't matter they won't change"

Perhaps, but I would be interested to know the exact format of the defined name Type; for example whether it is defined relatively or absolutely.

Other than that, it will be quite difficult to help you without seeing a workbook.

Regards

[daddylonglegs]

You will get #VALUE! error if the ranges are not the same size and shape, are you sure EXTRA1 and type are the same size and shape?

[Jp4Real]

@XOR LX: TYPE Refers to B14:B10000
and what is the relatively and absolutely that you're talking about?

@daddylonglegs i just checked the size and shape and there all exactly the same

@Jonmo1 I checked all the values for AD14:AD10000 they're all fine !

[Jonmo1]

Why not change it to a SUMIF instead of SUMIFS..You're only doing 1 criteria after all...

=SUMIF(type;C10;SLING)
=SUMIF(type;C10;EXTRA1)

[Jp4Real]

Thanks a lot didn't think of that it Worked !!

[Jonmo1]

Thanks a lot didn't think of that it Worked !!

What worked ?

[XOR LX]

Well, precisely that if "TYPE Refers to B14:B10000", as you say, then when a formula which references this named range is dragged to the right, that reference will become C14:C10000.

Formulas for Named Ranges are governed by the same rules as worksheet-entered formulas in terms of relative/absolute referencing (the 'dollar' signs).

Regards

[Jonmo1]

If it was using Sumif instead SumifS that worked, then you still have a problem that needs to be addressed.
Aren't you curious WHY Sumif works but Sumifs does not?

If this returns a number value
=SUMIF(type;C10;EXTRA1)
But this returns #VALUE! Error
=SUMIFS(EXTRA1;type;C10)

The logical conclusion confirms daddylonglegs' post #4, that the 2 named ranges (EXTRA1 and type) are not the same size/shape.
Because Sumif allows the criteriarange and sumrange to be different size/shape, but Sumifs does not.

And if this is true, then I have doubts that the Sumif is even returning the "correct" result.
It may be returning a number, but it might not be the correct number.

Can you post a copy of the workbook?


Or what do these formulas return?

These 2 should be the same result
=ROWS(EXTRA1)
=ROWS(type)

These 2 should be the same result
=COLUMNS(EXTRA1)
=COLUMNS(type)

[Jp4Real]

If it was using Sumif instead SumifS that worked, then you still have a problem that needs to be addressed.
Aren't you curious WHY Sumif works but Sumifs does not?

If this returns a number value
=SUMIF(type;C10;EXTRA1)
But this returns #VALUE! Error
=SUMIFS(EXTRA1;type;C10)

The logical conclusion confirms daddylonglegs' post #4, that the 2 named ranges (EXTRA1 and type) are not the same size/shape.
Because Sumif allows the criteriarange and sumrange to be different size/shape, but Sumifs does not.

And if this is true, then I have doubts that the Sumif is even returning the "correct" result.
It may be returning a number, but it might not be the correct number.

Can you post a copy of the workbook?


Or what do these formulas return?

These 2 should be the same result
=ROWS(EXTRA1)
=ROWS(type)

These 2 should be the same result
=COLUMNS(EXTRA1)
=COLUMNS(type)

Unfortunatly I can't try it right now i'll try to look at it tonight! Tanks a lot for your help

[Jp4Real]

maybe a little late but a yeaur later i've tried it,
These 2 should be the same result
=ROWS(EXTRA1) Gives me 9987,0
=ROWS(type) gives me 9980,0

These 2 should be the same result
=COLUMNS(EXTRA1) gives 1,0
=COLUMNS(type) gives 1,0

so obviously ROWS are differerent. but i've calculated the data and it works so i dont get the mistake....

thanks so much !