How to sum across a table with 2 criteria, one in rows, one in columns

[IvanM]

Hello everyone. My name is Ivan and an]m new to this forum. Could someone explain me where is the mistake in following formula:

=SUMIFS('Jan-16 Weekly'!$E:$DC;'Jan-16 Weekly'!$B:$B;$A24;'Jan-16 Weekly'!$10:$10;C$21)

Thanks a lot

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[southward]

Hello Ivan,

The first mistake I see is the use of semicolons ";" where commas "," should be separating the "sum_range, criteria_range1, and criteria1".

Please Login or Register  to view this content.

With out any data to work with it isn't possible to determine any other issues.

[Khalidngo]

Hi @southward,
You are right "without any data to work with it it is not possible to determine"
But Semicolons ";" in formula is not a mistake, OP's excel version uses semicolons instead of commas.

@IvanM
You have used multiple columns in sum_range (we can work with it by making it dynamic) but I think the main reason why your formula is not working is SUMIFS does not work with mixed array, all array must be either vertical or horizontal.

Regards,

[IvanM]

Thanks a lot. I also think mixed array is the problem. Is there any similar function that would work with mixed array?

[Khalidngo]

Yes, for example:
=SUMPRODUCT(

[IvanM]

Thanks. I have a table with sales.

days are represented in rows, different stores in columns. I want to sum daily sales to get it weekly in a different table. Can SUMPRODUCT do the job?

[Khalidngo]

As requested earlier, without having data it is difficult to answer, but here is my BWAG:
=SUMPRODUCT(('Jan-16 Weekly'!$E:$DC)*('Jan-16 Weekly'!$B:$B=$A24)*('Jan-16 Weekly'!$10:$10=C$21))

Regards,

[IvanM]

Thanks a lot

[southward]

Khalid,

Thanks for the info regarding Semicolons ";" being used in other excel versions. Good to know.

[zbor]

Khalid,
Thanks for the info regarding Semicolons ";" being used in other excel versions. Good to know.

It's not other excel version. It's another regional settings. , become ; and . become ,