Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif 2 criteria - 1 in a Row and 1 in a Column
Hi all,
Not sure if this can be done, but I am looking to sum quantities carried out in a certain period (month no. reference, for which there could be up to 25 columns of data per month) & then also against certain items of work in the rows (for which there could be 50+ work categories. eg. A B C D E F G H 1 Month 1 1 1 1 1 2 2 2 Supply 56 3 Make 12 20 18 6 4 Package 10 10 10 10 10 6 5 Sell 5 5 3 2 4 4 4 Hopefully that comes out okay. So I am after a formula that will SUMIF (or equivalent) All things 'Made' in month 1, and then also all things 'Packaged' in month 1, etc Only drama is: Row references aren't fixed. Excel will have to first lookup what row number 'Make' is in and then sum all values in that row where row A = 1. Hopefully I have explained this properly. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif 2 criteria - 1 in a Row and 1 in a Column
Hi,
Try this =SUMPRODUCT((A2:A5="Supply")*(B1:H1=1)*(B2:H5)) For clarity of i used "Supply" and 1 in the formula but these could be cell references. I wasn't sure if the 1 was a 1 or a date formated to show the month so this is the simple option of a 1 Mike "Qicea" wrote: Hi all, Not sure if this can be done, but I am looking to sum quantities carried out in a certain period (month no. reference, for which there could be up to 25 columns of data per month) & then also against certain items of work in the rows (for which there could be 50+ work categories. eg. A B C D E F G H 1 Month 1 1 1 1 1 2 2 2 Supply 56 3 Make 12 20 18 6 4 Package 10 10 10 10 10 6 5 Sell 5 5 3 2 4 4 4 Hopefully that comes out okay. So I am after a formula that will SUMIF (or equivalent) All things 'Made' in month 1, and then also all things 'Packaged' in month 1, etc Only drama is: Row references aren't fixed. Excel will have to first lookup what row number 'Make' is in and then sum all values in that row where row A = 1. Hopefully I have explained this properly. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif 2 criteria - 1 in a Row and 1 in a Column
Mike,
The formula returns the #NUM! error. My actual formula reads like this: =IF(T44=0,0,IF(G44=0,(SUMPRODUCT(('13LOG'!$A:$A='1 5PCS'!$R44)*('13LOG'!5:5='15PCS'!Z$41)*('13LOG'!$K $6:$II$999))),0)) So my work category references are all contained in column A of another sheet and the month numbers in row 5 of the same sheet. Obviously the sheet that the formula is in is a month by month tally of the records for each work category with the cateogry reference in column R and the month number in row 41, with the hope being that I can then just drag the formula down and it will lookup the values I want and sum the contents. I don't know if that changes anything. Maybe you can see what I've done wrong. Cheers Barry "Mike H" wrote: Hi, Try this =SUMPRODUCT((A2:A5="Supply")*(B1:H1=1)*(B2:H5)) For clarity of i used "Supply" and 1 in the formula but these could be cell references. I wasn't sure if the 1 was a 1 or a date formated to show the month so this is the simple option of a 1 Mike "Qicea" wrote: Hi all, Not sure if this can be done, but I am looking to sum quantities carried out in a certain period (month no. reference, for which there could be up to 25 columns of data per month) & then also against certain items of work in the rows (for which there could be 50+ work categories. eg. A B C D E F G H 1 Month 1 1 1 1 1 2 2 2 Supply 56 3 Make 12 20 18 6 4 Package 10 10 10 10 10 6 5 Sell 5 5 3 2 4 4 4 Hopefully that comes out okay. So I am after a formula that will SUMIF (or equivalent) All things 'Made' in month 1, and then also all things 'Packaged' in month 1, etc Only drama is: Row references aren't fixed. Excel will have to first lookup what row number 'Make' is in and then sum all values in that row where row A = 1. Hopefully I have explained this properly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use Sumif and have the criteria be 10 AND <26? | Excel Discussion (Misc queries) | |||
Sumif from two criteria in one column | Excel Discussion (Misc queries) | |||
sumif with multiple criteria in one column | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
sumif with 2 criteria | Excel Worksheet Functions |