Sumproduct formula

[jgomez]

Hi there,

=SUMPRODUCT(--(E3<G3:G5000),--(ISNUMBER(SEARCH("Beaumont",H3:H5000))),D3:D5000)

This formula works well except I forgot to have it check Column “I” first… if column “I” has “Yes” then I don’t wan to add… even though E3<G3:5000….

=SUMPRODUCT(--(H3:H5000="no"),--(ISNUMBER(SEARCH("Olivia",G3:G5000))),F3:F5000)

This formula looks for anything containing Olivia… so if that column has “Olivia/off” or just “Olivia” it will sum the balance in rows “F3:F5000”.. how can I adjust to look for exactly “Olivia”… if it has “Olivia/Off” I don’t want to add…

[NBVC]

=SUMPRODUCT(--(I3:I5000<>"Yes"),--(E3<G3:G5000),--(ISNUMBER(SEARCH("Beaumont",H3:H5000))),D3:D5000)

and

=SUMPRODUCT(--(H3:H5000="no"),--(G3:G5000="Olivia"),F3:F5000)

[jgomez]

okay the 2nd formula worked but I had to add more rows and sfuff so I changed the cell range from:

=SUMPRODUCT(--(H3:H5000="no"),--(G3:G5000="Olivia"),F3:F5000)

to

=SUMPRODUCT(--(I3:I5000="no"),--(E6:E5000="Olivia"))

I just wanted to count them don't need the totals on column "F" and it worked until I modified it... all i get is #N/A...

[NBVC]

Do you get #N/A or #VALUE?

It looks like you have missized ranges..

Should be perhaps:

=SUMPRODUCT(--(I3:I5000="no"),--(E3:E5000="Olivia"))

[jgomez]

its my spreadsheet, tried that formula in 3 blank sheets and it works... not sure why. Thanks though.