Multiple SUMIF?!

[rjmills18]

hi
is there anyway to sum the total of certain cells in one column if they meet criteria in two other columns?!
for example
F...........I...........U
736......CMP......-250
736......REC.......-100
729......CMP......-150
736......CMP......-200
711......CMP........-50
736......ZAC.......-150

i need to sum the total of the cells in column U, where column F is 736 AND column I is CMP?!
how can i go about this?
thanks

[NBVC]

Try Sumproduct()

=Sumproduct((F1:F100=736)*(I1:100="CMP")*(U1:U100))

adjust ranges to suit

[rjmills18]

it is returning #VALUE,
the range is to cell 1000, and the 736 etc are formatted as text, here is what i put:
=SUMPRODUCT((F1:F1000="736")*(I1:I1000="CMP")*(U1:U1000))
cheers

[NBVC]

Does U1 have text in it (a column header perhaps?)

Try:

=SUMPRODUCT((F1:F1000="736")*(I1:I1000="CMP"),(U1:U1000))

[rjmills18]

thanks colin, the #VALUE has gone but it now returns nothing when there are cells that meet the criteria???!!

[NBVC]

colin? whose colin?

anyways... are you sure there are no extra spaces before after the 736 or CMP entries.?

[Colin Legg]

Hi,

I'm Colin. I posted a reply on this thread at 4:02 - exactly the same time as you. It appears that it has been moderated / removed?

Colin

Edit:
Hi NBVC,

Re. the information you provided in #8, I will quickly summarise the 'missing post' in here to improve the readability of the thread. Thanks.

Essentially my reply was:
=====
The most likely reason for that formula returning #VALUE is that you have column headers, ie. Cell U1 contains text. To cater for this try amending your formula to this:

=SUMPRODUCT(-(F2:F1000="736"),-(I2:I1000="CMP"),(U2:U1000))
=====

So our replies complimented each other's nicely!

[NBVC]

hmmm...strange... I didn't see that post..... maybe because you say it was exact same time, somehow it got blown away. Not sure...

[rjmills18]

there are no spaces in any cell, these entries are selected from a drop-down list. starting the array at F3 etc removed the ~value but i am still getting a 0 returned!!