SUMIFS / SUMPRODUCT Based on Three Sets of Criteria - Not Working!

Hello!

I'm trying to look up values based on three different criteria across two sheets.

I've managed to do this using SUMIFS, which will generate the results based on two types of criteria, but I'm unable to generate the value based on the value of the third criteria.

Basically, I want to generate my result based on the third factor (column F) having a particular value, otherwise wanting the result to be blank (on top of the other two columns being used).

I've attempted this using the following two formulas, neither giving me any success. Spreadsheet attached should this be helpful.


=SUMIFS(Table7[PLOT TOTAL],Table7[SITE],[@SITE],Table7[PLOT - FOR ALL ELSE],[@[PLOT(S)]],[TRANSACTION TYPE],"contract")

=SUMPRODUCT(--(Table7[SITE]=[@SITE]),--(Table7[PLOT - FOR ALL ELSE]=[@[PLOT(S)]]),--([@[TRANSACTION TYPE]]="contract"),Table7[PLOT TOTAL])


Hope this is clear. I'm sure there is an easy way to do this (bit of a noob here) - thank you all in advance

Regards,
Luke

from one Colcestrian Luke to another.... ;-)

the third criteria seems pre-emptive (and stored separate to Table 7) so, rather than being part of the SUMIFS this test should likely precede it, logically speaking, e.g.

=IF([@[TRANSACTION TYPE]]<>"contract","",SUMIFS(Table7[PLOT TOTAL],Table7[SITE],[@SITE],Table7[PLOT - FOR ALL ELSE],[@[PLOT(S)]]))

Ha ha - what are the chances!!

That's fantastic. Got lots more of those formulas to do, so will remember that.

One more thing... I'm having a similar issue except the sum range concerns two different columns for one result.
Similar to before, I'm trying to generate a value based on the 'transaction type'. However, I need to look up one specific value from two different columns in the 'lookup' sheet based on the column header name.

I know this isn't the most logical way of doing things, but am totally stumped. Is there a simple way of achieving this? Currently, I'm looking up the labour total, which isn't ideal!

do you mean something like below?

Formula:

Please Login or Register  to view this content.

the above will sum the column whose name matches that of the row Transaction Type -- so 5 and 10, respectively, in your sample
(you could also use an INDIRECT but, the above is the better alternative, IMO)

Hi people!

Sorry Ali, this was a tricky one for me to explain!

XLent has cracked this for me though. I've been using this without any issues so far.

Thanks so much for your help