SUMIF or SUMPRODUCT

[adrienne0914]

Hi all,
I am trying to figure out the best formula for this problem. My workbook is more complicated, but in a nutshell here is what I'm trying to create a formula for:

Data Tab

ANIMAL	COUNT
Cats	18
Dogs	245
Rabbits	17

<tbody>
</tbody>

Animals tab

Cats	Y
Dogs	N
Bears	Y

<tbody>
</tbody>

Month tab

Jan	y
Feb	Y
Mar	N

<tbody>
</tbody>

	CM1	CM2
Cats	50	75
Dogs	42	18
Bears	22	37

<tbody>
</tbody>

If animal=Y and month=Y, multiply COUNT by CM1, otherwise multiply COUNT by CM2. So for instance, both conditions are met by cat. So multiply 18*50.

I hope someone can help.

 

[Aryatect]

Hi,

I have used combination of IF and VLOOKUP to get to this, and for Month I have used today's date (=TODAY()) if you want you can change that with any particular month or refer to a cell where month is given. Just to see whether this works I had kept everything on the same tab. See if it fulfills your requirement:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ANIMAL	COUNT	Answer			Cats	Y				Jun	y			CM1	CM2
2	Cats	18	900			Dogs	N				Feb	Y			Cats	50	75
3	Dogs	245	4410			Rabbits	Y				Mar	N			Dogs	42	18
4	Rabbits	17	374												Rabbits	22	37

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C2 =IF(AND(VLOOKUP(A2,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B2*VLOOKUP(A2,$O$2:$Q$4,2,FALSE),B2*VLOOKUP(A2,$O$2:$Q$4,3,FALSE)) C3 =IF(AND(VLOOKUP(A3,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B3*VLOOKUP(A3,$O$2:$Q$4,2,FALSE),B3*VLOOKUP(A3,$O$2:$Q$4,3,FALSE)) C4 =IF(AND(VLOOKUP(A4,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B4*VLOOKUP(A4,$O$2:$Q$4,2,FALSE),B4*VLOOKUP(A4,$O$2:$Q$4,3,FALSE)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[adrienne0914]

Thanks for the replay, Aryatect. I think this will work, but I'm having problems translating to multiple tabs with my file setup. I'll let you know if it works.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ANIMAL	COUNT	Answer			Cats	Y				Jun	y			CM1	CM2
2	Cats	18	900			Dogs	N				Feb	Y			Cats	50	75
3	Dogs	245	4410			Rabbits	Y				Mar	N			Dogs	42	18
4	Rabbits	17	374												Rabbits	22	37

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C2 =IF(AND(VLOOKUP(A2,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B2*VLOOKUP(A2,$O$2:$Q$4,2,FALSE),B2*VLOOKUP(A2,$O$2:$Q$4,3,FALSE)) C3 =IF(AND(VLOOKUP(A3,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B3*VLOOKUP(A3,$O$2:$Q$4,2,FALSE),B3*VLOOKUP(A3,$O$2:$Q$4,3,FALSE)) C4 =IF(AND(VLOOKUP(A4,$F$1:$G$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),$K$1:$L$3,2,FALSE)="Y"),B4*VLOOKUP(A4,$O$2:$Q$4,2,FALSE),B4*VLOOKUP(A4,$O$2:$Q$4,3,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

 

[Aryatect]

I have moved the data as per your tabs named Data, Animals, Months & CM tab (there was no name so just adding this) then the final formula will look like below:

	A	B	C
1	ANIMAL	COUNT	Answer
2	Cats	18	900
3	Dogs	245	4410
4	Rabbits	17	374

<tbody>
</tbody>

Data​

Worksheet Formulas Cell Formula C2 =IF(AND(VLOOKUP(A2,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B2*VLOOKUP(A2,'CM Tab'!$A$2:$C$4,2,FALSE),B2*VLOOKUP(A2,'CM Tab'!$A$2:$C$4,3,FALSE)) C3 =IF(AND(VLOOKUP(A3,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B3*VLOOKUP(A3,'CM Tab'!$A$2:$C$4,2,FALSE),B3*VLOOKUP(A3,'CM Tab'!$A$2:$C$4,3,FALSE)) C4 =IF(AND(VLOOKUP(A4,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B4*VLOOKUP(A4,'CM Tab'!$A$2:$C$4,2,FALSE),B4*VLOOKUP(A4,'CM Tab'!$A$2:$C$4,3,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

Happy to Help !!

 

[adrienne0914]

Thanks! I was able to work out the first part of the formula. My issue is with the B2. In my workbook, it won't be looking at a single cell. It will have to search a column on another tab (say Data) for a match, then go to the 'CM Tab' other part of the multiplication. Would that be another VLOOKUP?

Worksheet Formulas Cell Formula C2 =IF(AND(VLOOKUP(A2,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B2*VLOOKUP(A2,'CM Tab'!$A$2:$C$4,2,FALSE),B2*VLOOKUP(A2,'CM Tab'!$A$2:$C$4,3,FALSE)) C3 =IF(AND(VLOOKUP(A3,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B3*VLOOKUP(A3,'CM Tab'!$A$2:$C$4,2,FALSE),B3*VLOOKUP(A3,'CM Tab'!$A$2:$C$4,3,FALSE)) C4 =IF(AND(VLOOKUP(A4,Animals!$A$1:$B$3,2,FALSE)="Y",VLOOKUP(TEXT(TODAY(),"mmm"),Months!$A$1:$B$3,2,FALSE)="Y"),B4*VLOOKUP(A4,'CM Tab'!$A$2:$C$4,2,FALSE),B4*VLOOKUP(A4,'CM Tab'!$A$2:$C$4,3,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

Happy to Help !!

 

[Aryatect]

Yes, you are right, that here I had used B2 as there was a single value there in the same table, if it is coming from some other tab, then B2 will be replaced by another VLOOKUP to get that value which will be used for calculation.