| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 77191 | 2007-03-01 20:13:00 | Excel Guru's - SumIF Multiple criteria | SolMiester (139) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 529218 | 2007-03-01 20:13:00 | Help, my formula works okay with just 1 criteria, but I cant get multiple criteria to work. Range Data!C2:F11115 Criteria B5*data!c2:c11115<20000 Sum_Range Data!J2:J11115 Criteria is column F = B5 AND column C < 20000 using * as AND cant be put in criteria?! Help... |
SolMiester (139) | ||
| 529219 | 2007-03-01 23:10:00 | I had a muck around with SUMIF & it seems that you're pretty limited with the Criteria field. I suggest you add a column with the IF condition you want to use, then sum that column. Failing that, try looking at www.ozgrid.com |
MushHead (10626) | ||
| 529220 | 2007-03-02 00:51:00 | The best way to get multiple criteria to work over a range is not to use sumif but the sumproduct function. =sumproduct( --(criteria 1) , --(criteria 2), range to sum over) So for your example it would be something along the lines of =sumproduct( --(F2:F11115 = B5), --(c2:c11115<20000), J2:J11115) I bet you are wondering what the double negative is for. Its a clever trick in Excel to evaluate the range where the first negative turns the expression into an array of true/false statements according to the criteria and the second turns it into an array of zeros and ones. So with matrix multiplication, where both criteria are met you get 1 x 1 x value = value. If the first criteria isn't met and the second one is you get 0 x 1 x value = 0. And all of these are then summed up! Cunning eh! Note you can have as many criteria as you like using this method! |
odyssey (4613) | ||
| 529221 | 2007-03-04 20:32:00 | Hi Thanks all, great work, I will have a go with sumproduct Hmmmm =SUMPRODUCT(Data!C2:C8153<20000,Data!F2:F11115=STAFF!B5,(Data!J2:J11115)) getting #value??? |
SolMiester (139) | ||
| 529222 | 2007-03-04 20:42:00 | erh.....dont think sumproduct will work for me here.. | SolMiester (139) | ||
| 529223 | 2007-03-05 23:57:00 | Formula should be something like: =SUMPRODUCT(--(Data!C2:11115<20000),--(Data!F2:F11115=STAFF!B5),Data!J2:J11115) You need to have the double negative and the criteria in brackets for this to work. Arrays should also be of the same size too. Hopefully this should now work for you... |
odyssey (4613) | ||
| 529224 | 2007-03-06 04:35:00 | Oops, just noticed I inadvertently left of the column c in the formula - try: =SUMPRODUCT(--(Data!C2:C11115<20000),--(Data!F2:F11115=STAFF!B5),Data!J2:J11115) |
odyssey (4613) | ||
| 529225 | 2007-03-06 04:43:00 | Oops, just noticed I inadvertently left of the column c in the formula - try: =SUMPRODUCT(--(Data!C2:C11115<20000),--(Data!F2:F11115=STAFF!B5),Data!J2:J11115) Odyssey, you are the man!....That is just the bomb equation mate, thank-you very very much....:beer: |
SolMiester (139) | ||
| 529226 | 2007-03-06 09:10:00 | Hi, your answer has already been resolved but just my two cents worth. If your wanting to do a single criteria then SUMPRODUCT(--(range=criteria)) or SUMPRODUCT(N(range=criteria)) can be used, but of course you can use COUNTIF for that so using double negatives or N becomes redundant. When you have multiple criteria then * means AND and + means OR. To count items then just use all the conditions within one argument like this... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria)) to achieve a COUNT or include a second argument to indicate you want to sum the result if all the criteria in the first argument is true.... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria),range ) for a SUM. regards, Graham |
Parry (5696) | ||
| 1 | |||||