Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using multiple criteria and its own range
Using Excel 2003 trying to find a way of adding numbers in a row that match a
month and are less than 100. Ican Sumif matching the month but need to just add together those above or below 100. So far i have =SUMIF('Out and About'!B4:Y4:'Out and About'!B27:Y27,"January",'Out and About'!B11:Y11:'Out and About'!B34:Y34) but need the next part. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using multiple criteria and its own range
On Dec 28, 4:23*pm, Yerman wrote:
Using Excel 2003 trying to find a way of adding numbers in a row that match a month and are less than 100. Ican Sumif matching the month but need to just add together those above or below 100. So far i have =SUMIF('Out and About'!B4:Y4:'Out and About'!B27:Y27,"January",'Out and About'!B11:Y11:'Out and About'!B34:Y34) but need the next part. I'm not sure which range is which from your formula but, in general, you can use SUMPRODUCT to get a sum with more than one criteria, i.e. =SUMPRODUCT(--(date_range="January"),--(sum_range<100),sum_range) I'm assuming that date_range contains just the month as text, not a date |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif using multiple criteria and its own range
Hi,
FYI the proper syntax for a range like you are using is 'Out and About'!B4:Y427' However, this is moot for you case, since you can't use SUMIF to do what you want. Instead you could use SUMPRODUCT or DSUM For DSUM the formula might look like this =DSUM('Out and About'!A1:Y99,B1,W1:X2) In this case the data area with titles runs from A1:Y99, and here I have assumed the numbers are in column B (hence the B1 reference.) In cells W1:X2 you enter your criteria, in this case: Month Amount January <100 If this helps, please click the Yes button Cheers, Shane Devenshire "Yerman" wrote in message ... Using Excel 2003 trying to find a way of adding numbers in a row that match a month and are less than 100. Ican Sumif matching the month but need to just add together those above or below 100. So far i have =SUMIF('Out and About'!B4:Y4:'Out and About'!B27:Y27,"January",'Out and About'!B11:Y11:'Out and About'!B34:Y34) but need the next part. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
sumif when criteria is a range | Excel Discussion (Misc queries) | |||
SUMIF with AND for range and criteria | Excel Worksheet Functions | |||
SUMIF multiple criteria in 1 range | Excel Worksheet Functions |