Multiple SUMIF?!

**rjmills18** · 07-17-2008, 10:42 AM

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** · 07-17-2008, 10:45 AM

Try Sumproduct()

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

adjust ranges to suit

**rjmills18** · 07-17-2008, 10:56 AM

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** · 07-17-2008, 11:02 AM

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

Try:

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

**rjmills18** · 07-17-2008, 11:05 AM

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

**NBVC** · 07-17-2008, 11:08 AM

colin? whose colin?

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

**Colin Legg** · 07-17-2008, 11:10 AM

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** · 07-17-2008, 11:15 AM

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** · 07-17-2008, 11:18 AM

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!!

**NBVC** · 07-17-2008, 11:21 AM

Are the values in column U formatted as text or numbers...

Try selecting column U and go to Data|Text to Columns... click Finish...

does that change anything?

If not, then perhaps attach your sheet here and we can diagnose it better.

**rjmills18** · 07-17-2008, 11:29 AM

how do i make it small enough to attach?!

**NBVC** · 07-17-2008, 11:31 AM

Try zipping it... if it still not less than 100kb... you will have to slim the spreadsheet down to show only some of the records (including ones that should be counted).

**rjmills18** · 07-17-2008, 11:43 AM

think that should do it?!

**NBVC** · 07-17-2008, 11:48 AM

I thought you said that 736 was a text entry?... well it seems it is not... it is numeric...

when I took away the quotes for "736" in the formula, I got a non-zero result...

Try: =SUMPRODUCT((F4:F1000=736)*(I4:I1000="CMP")*(U4:U1000))

**rjmills18** · 07-17-2008, 11:56 AM

brilliant, although some of the entries in that column i will need to search for are text, "PLT" for e.g. is one. if i convert the numbers to text and use "" int the formula should that work....
...also, i was hoping to develop the formula so that if the end user selects the criteria from a dropdown list it changes the sum of column to the relevant month they have just selected?
i.e.736 and CMP remain the same byt if they choose MAY 08 it returns the sum of column W that meets the criteria?!
thanks so much!!

**rjmills18** · 07-17-2008, 12:00 PM

this will be a figure for cell H4 in monthly post if that helps it to make sense?
If the user switches from Apr 08 to may 08 i need the figure to search the same criteria in the new months column???!!!

**NBVC** · 07-17-2008, 12:10 PM

Try:

Please Login or Register  to view this content.

Note: You can replace "Dec 2008" with the cell reference where the user makes the change...

Also, 736 and "CMP" can be replaced with Cell references containing those values... then there is no need to change the numbers to text in column F.

**rjmills18** · 07-18-2008, 05:12 AM

thank you so much that is absolutely brilliant!!

**rjmills18** · 07-18-2008, 05:19 AM

this formula is working for every month except apr 08?! can you suggest why?!
thanks again!

**rjmills18** · 07-18-2008, 06:08 AM

sorry, the array didnt include apr 08!
i have a little issue now tho that when i drag the formula down into other cells it is returning a zero value?! here is the code, pre-drag, as you will gather it is another sheet:...
=SUMPRODUCT(('Dpn Reg'!$F$4:$F$1000=D4)*('Dpn Reg'!$I$4:$I$1000=C4)*(INDEX('Dpn Reg'!$U$4:$IU$1000,,MATCH(A4,'Dpn Reg'!$U$2:$IU$2,0))))
cheers
millsy