Sumproduct with multiple variants - Help! I'm a newbie to sumproduct

Hi All, I'm new here.
my manager sent me a spreadsheet to play with a formula to include extra criteria. I've never used sumproduct, and my gogglefoo isn't working as I can't wrap my brain around it . I'm more a vlookup girl.

current formula is:
=SUMPRODUCT((SUBTOTAL(3,OFFSET($E$16:$E$1619,ROW($E$16:$E$1619)-MIN(ROW($E$16:$E$1619)),,1)))*(($E$16:$E$1619)=$F$4)*(P$16:P$1619))

wherein
Column E holds values, e.g. E90, E00, E75 etc.
$F$4 refers to a cell that lists E90 as a value
column p contains the sum total of each row

now I need to add in to also look for E00 in column E but only if a certain value in colum D as E00 has some negative amounts that need to be applied. However, applies to various sectors not just CS wind..

I've tried below, based on what I could deduce on sumproduct formulas, but somewhere I'm going wrong, and probably missing some brackets.

=SUMPRODUCT((SUBTOTAL(3,OFFSET($E$16:$E$1619,ROW($E$16:$E$1619)-MIN(ROW($E$16:$E$1619)),,1)))*(($E$16:$E$1619)=$F$4)*($E$16:$E$1619)=$G$4)*(($D$16:$D$1619)=$F$2)*(P$16:P$1619)
wherein
Column E holds the first value, E00
$g$4 refers to a cell that lists E00 as a value
column D holds the second value
$F$2 refers to a cell that lists said second value
column p contains the sum total of each row

I've also tried with if(or(and but that came totally unravelled

Any suggestions?

I have uploaded truncated file (sample.xlsx) as I had to remove sensitive information. the cells highlighted in yellow

Thank you

When you are dealing with arrays, it is important to note that * is equivalent to AND and + is equivalent to OR - you can't use the AND or OR functions as they can only be used on single cells.

This part of your formula:

(($E$16:$E$1619)=$F$4)*(($E$16:$E$1619)=$G$4)

(and you are right: you missed a bracket after the * which I've added in red) is basically saying "(column E cells are equal to F4) AND (column E cells are equal to G4)" which can never be true if F4 is not equal to G4 - I think you need to consider OR (+) here, and put an extra set of brackets at the beginning and end of the expression within your formula. There should also be an extra close bracket at the end of your formula.

Hope this helps.

Pete

no wonder my head hurt when * = + and + equals or . I definitely have a steep learning curve ahead. In the meantime I played with sumifs and found a (not so elegant but working) solution for the interim.

Thanks FlameRetired, I have bookmarked that page!