Sumif 2 criteria - 1 in a Row and 1 in a Column

[Qicea]

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.

 

[Mike H]

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]

Mike,

The formula returns the #NUM! error.
My actual formula reads like this:

=IF(T44=0,0,IF(G44=0,(SUMPRODUCT(('13LOG'!$A:$A='15PCS'!$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