SUMIFS - double counting?

Originally Posted by oeldere

With an helpcolumn (I used VLookup to determine the values).

After that I made an pivot table with the sum and average.

See the attached file.

Thank you for preparing that file oeldere. It is not arriving at the answer I am trying to get however. Mazzaropi arrived at the right answer though. I think you are on the right track, but I don't really understand the use of the HELPCOLUMN and VLOOKUP. Is it possible to just use a formula for this? Similar to the formula I have used?

Originally Posted by Mazzaropi

[B]
At first part of formula it sums 144,48 and second part 28,1
Then 144,48 - 28,1 = 116,38

The expected result value would be 71,82?

Yes the expected result would be 71.82. Is it possible to arrive at the correct result by using a single formula rather than using a pivot table. Ideally the result would be arrived at within a single cell (the one which contains the formula). I suppose the porblem is that SUMIFS does not use the "OR" operation, only the "AND."

Does this make sense?

Thank you,

Geoff.