Sumifs multiple sheets

Hello,
Please someone to help me with the below formula:

SUMPRODUCT(SUMIFS(INDIRECT("'"&sheets&"'!"&"S15:S73"),INDIRECT("'"&sheets&"'!"&"U15:U73"),E$2,INDIRECT("'"&sheets&"'!K7"),$C3,INDIRECT("'"&sheets&"'!K10"),{"703-GB","703 EDIE BULK"}))*60

The result from the formula is an error #VALUE!. I have checked the formula several times, but I can't find any mistake.

I've attached the file with the example.

Thanks in advance for your help

All the ranges must be same dimensions: 2 of yours are single cells.

It is not clear what you are trying to do.

Originally Posted by JohnTopley

All the ranges must be same dimensions: 2 of yours are single cells.

It is not clear what you are trying to do.

Hello JohnTopley,
What I am trying with this formula is:
Imagine you have to fill the stoppages of one machine. The stoppages A,B,C.....
You have three working shifts: 1,2,3. But also on this line you are producing different kind of products (different formats).
What I am trying to do is to sum the time by stoppages, by shift and by kind of product. As you can see from the formula for this machine the formats are 703-GB and 703 EDIE BULK. All other possible formats must be excluded.
So if I have for example in cell K10 a format different from the mentioned above, the formula shouldn't sum the requested information (time, stoppages, shift)
If you have any idea how to manage this it will be perfect.

Originally Posted by avk

In "sumif" Sheet in "E3"

Formula:

Please Login or Register  to view this content.

Array formula shift+ctrl+enter
copy paste across.

Hello avk,
The SUMIF formula doesn't work for me. I need more than one criteria and because of that I should use SUMIFS.
Thanks anyway for your reply.

SUMIFS () does not love array mode (CSE or SUMPRODUCT) so you need to divide SUMIFS () into SUMIF () the right number of times.

maybe this one (check for ranges)

Formula:

Please Login or Register  to view this content.

and now you can (should) use Control+Shift+Enter but no SUMPRODUCT

You cannot simply compare K7 to C3 OR k10 to 703-GB and 703 EDIE BULK: you need compare to ranges like S15:S73.


I am still no wiser!

Originally Posted by sandy666

Because I don't know what result you want to achieve, maybe this one:

Please Login or Register  to view this content.

with:

Formula:

Please Login or Register  to view this content.

And you do not need to use apostrophes because your sheet names do not contain spaces

Hello sandy666,
This formula doesn't work for me, because what I want is to sum S15:S73 from all sheets if the three conditions are available at the same time ( but not one of them). Some kind like sumif + and function...
Sum S15:S73 if K7=C3 and K10={"703-GB","703 EDIE BULK"} and U15:73=E2.
In your formulas you are summing the results from the three conditions.
Thanks anyway.

Ok, could you show expected results?

Originally Posted by sandy666

Ok, could you show expected results?

Hello sandy666,
As attached you can find the requested result.

Regards,
Aneliya

Originally Posted by sandy666

1st and important, like John said in post#2 all ranges must be the same size

cite from MS

so formula is (with reorganization your data a little)

Formula:

Please Login or Register  to view this content.

columns V & W are hidden

Thank you a lot for the help. It works.
Thanks to all of you and especially to sandy666.

If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) Thanks.

Thanks for the feedback and mark thread solved