Searching for value in cell from using two criterias

[ablount]

I am trying to fix my excel workbook that changes weekly.Sometime the data tab will have items that were not there the week prior so therows change. I use =SUM('MASTER FEED'!E658) to get the value but the cellchanges and I have to check it weekly to see if the value I am getting iscorrect. I tried to use sumif and sumifs but I don’t need the range since theychange, I need to search the item name. For example, I have red, blue, andgreen pens. I need to know the total amount of each pen but some weeks I pullthe data and we do not carry green pens so the other items below that will bewrong. Is there a formula that will search column B for “Pens” then look for “green”(column C) within those range of pens and give me the amount that is in thecell to the right of green? The issue is I could also get bad data if I amsearching everything for “green” since binders as well could be green. I hope this isn’t confusing but PLEASE HELP!I spend at least 2 hours a week on this verifying the data when I know it canbe done with a formula.

 

[onlyadrafter]

Hello,

does the following work as expected?

In Col B, you will have Pens, Binders etc

In Col C, you will have Red, Green etc

In H1 enter Pens. In H2 enter green

In H3 enter this formula:

=SUMPRODUCT(--($B$1:$B$6=H$1)*(--($C$1:$C$6=H$2)*$D$1:$D$6))

you can then add other criteria in Cols I onwards and copy the formula over.

Change cell range as required, but they must be the same row numbers.

 

[ablount]

Can I send you the product in an email? I am new to the site and can't upload it. I tried what you gave me in the cell and it does not work. It maybe me, not the formula.

 

[MimiM]

I have tried the following - assuming you start at A1.

Column
A B C D E F G
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Item[/TD]
[TD="class: xl65, width: 64"]Colour[/TD]
[TD="class: xl65, width: 64"]Items[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]binder[/TD]
[TD="class: xl65"]red[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Pen[/TD]
[TD="class: xl65"]Binder[/TD]
[TD="class: xl65"]Colour[/TD]
[/TR]
[TR]
[TD="class: xl65"]pen[/TD]
[TD="class: xl65"]green[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]18[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"]Red[/TD]
[/TR]
[TR]
[TD="class: xl65"]pen[/TD]
[TD="class: xl65"]blue[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65"]Green[/TD]
[/TR]
[TR]
[TD="class: xl65"]binder[/TD]
[TD="class: xl65"]green[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]30[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65"]Blue[/TD]
[/TR]
[TR]
[TD="class: xl65"]pen[/TD]
[TD="class: xl65"]red[/TD]
[TD="class: xl65, align: right"]18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]pen[/TD]
[TD="class: xl65"]blue[/TD]
[TD="class: xl65, align: right"]20[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]binder[/TD]
[TD="class: xl65"]blue[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


The formulae in E and F are as follows:

E3 - =SUMIFS(C:C,A:A,E$2,B:B,G$3)
F3 - =SUMIFS(C:C,B:B,$G3,A:A,F$2)

The item headers are in E2 and F2
The colour headers are in G3 to 5

Hope this helps; I'm still new and not certain how to include tables

Mimi