SUMIF function
Hi everyone, first post for me, I have been using the sumif function the last couple days and I've was wondering if there is anyway to have more than 1 range and criteria for this function or is there another function where I can do this? Below might better illustrate what I'm trying to do: Column 1 Column 2 Column 3 Blue One 100 Blue One 100 Red One 100 Red Two 200 I want the sum of column 3, if column 1 equals blue AND column 2 equals One. So my answer in this case is 200. -- yak10 |
Try:
Sumproduct((Column1 Range="Blue")*(Column2 Range="One")*(Column3 Range)) All three ranges must be the same length HTH Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "yak10" wrote in message ... Hi everyone, first post for me, I have been using the sumif function the last couple days and I've was wondering if there is anyway to have more than 1 range and criteria for this function or is there another function where I can do this? Below might better illustrate what I'm trying to do: Column 1 Column 2 Column 3 Blue One 100 Blue One 100 Red One 100 Red Two 200 I want the sum of column 3, if column 1 equals blue AND column 2 equals One. So my answer in this case is 200. -- yak10 |
You need to invoke a differen type of formula for SumIf does not admit
more than one condition... =SUMPRODUCT(--(ColorRange=Color),--(TextNumRange=TextNum),SumRange) yak10 wrote: Hi everyone, first post for me, I have been using the sumif function the last couple days and I've was wondering if there is anyway to have more than 1 range and criteria for this function or is there another function where I can do this? Below might better illustrate what I'm trying to do: Column 1 Column 2 Column 3 Blue One 100 Blue One 100 Red One 100 Red Two 200 I want the sum of column 3, if column 1 equals blue AND column 2 equals One. So my answer in this case is 200. |
Hi,
Array entet the following formula (Ctrl+Shift+Enter) =SUM(IF((A2:A6=A8)*(B2:B6=1),C2:C6)) You can also use the DSUM function. Regards, "yak10" wrote: Hi everyone, first post for me, I have been using the sumif function the last couple days and I've was wondering if there is anyway to have more than 1 range and criteria for this function or is there another function where I can do this? Below might better illustrate what I'm trying to do: Column 1 Column 2 Column 3 Blue One 100 Blue One 100 Red One 100 Red Two 200 I want the sum of column 3, if column 1 equals blue AND column 2 equals One. So my answer in this case is 200. -- yak10 |
All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com