[Solved] Need help with SUMPRODUCT() and/or SUMIFS()

[go2visions]

The problem is outlined in the attachment

Untitled 1.ods

(13.88 KiB) Downloaded 105 times

Any help will be appreciated.

Thank you!!

[FJCC]

If I understand what you want, here is one possible approach which uses the fact that a comparison that is False returns a zero.

Edit: By the way, I removed the leading spaces from the text in G5:P5. That is an illustration of how error prone this kind of thing is.

[robleyd]

You have exactly the same formulae in all of G6:P6 including absolute references; is this really what you want?

If not, perhaps you could describe what you are trying to achieve.

[go2visions]

Sorry for not explaining exactly what I need before.

Please look at the revised attachment.

Untitled 1.ods

Updated file with better explanation of problem

(13.88 KiB) Downloaded 116 times

[FJCC]

This formula should be entered in G6

Code: Select all

=SUMIFS($D1:$D$100;$B$1:$B$100;".*"&$G$4&".*";$C$1:$C$100;G5)

It can then be copied to the other cells in row 6. You have to remove the leading space from G5:P6 for the formula to work. The formula returns a zero and not a blank when there are no items matching the requirements. There are ways to avoid that if it is truly necessary.

[go2visions]

Excellent solution, as usual!!

Thank you VERY much!!