SUMPRODUCT Formula & Count IF

[itsgrady]

=SUMPRODUCT(--(vpi!F:F="Apple Green"),vpi!K:K)+SUMPRODUCT(--(vpi!F:F="Redfish"),vpi!K:K)+SUMPRODUCT(--(vpi!F:F="Dark Blue"),vpi!K:K)+SUMPRODUCT(--(vpi!F:F="Green"),vpi!K:K)

=COUNTIF(vpi!F:F, "Acid")

SUMPRODUCT - The above formula works well in adding the weight (vpi!K:K) of the following items for one line on the spreadsheet:

• Apple Green
• Redfish
• Bark Blue
• Green

On another line of the spreadsheet I need the same sum information but I need to eliminate anything in a different column and sheet (vpi!N:N) that has "TT" in the column. How can I add to this formula to accomplish this?

COUNTIF - The above formula works for one line on the spreadsheet but I need the same information but I don't want to count anything in a different column and sheet (vpi!N:N) that has "TT" in the column. How can I add to this formula to accomplish this?

Thanks for the needed help.

 

[etaf]

how about adding an extra condition - heres just 2 of the sumproducts - extend to all the sumproduct
=SUMPRODUCT(--(vpi!F:F="Apple Green"),--(vpi!N:N<>"TT"),vpi!K:K)+SUMPRODUCT(--(vpi!F:F="Redfish"),--(vpi!N:N<>"TT"),vpi!K:K)

On 1 sheet as an example

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																24
3						apple green					1			tt
4
5
6						redfish					20			tt
7						apple green					1
8
9
10						redfish					20			tt
11						apple green					1
12
13
14						redfish					20
15						apple green					1
16
17
18						redfish					20			tt
19						apple green					1
20
Sheet1

Cell Formulas
Range	Formula
P2	P2	=SUMPRODUCT(--(F:F="Apple Green"),--(N:N<>"TT"),K:K)+SUMPRODUCT(--(F:F="Redfish"),--(N:N<>"TT"),K:K)

or without TRUE/FALSE conversion using --
=SUMPRODUCT((F:F="Apple Green")*(N:N<>"TT"),K:K)

 

[Peter_SSs]

Firstly, I would recommend not using whole column references, especially for SUMPRODUCT. Your formula requires Excel to check over a million cells and then do over a million multiplications and then add over a million results and then repeat all that 4 times!

Your original formula could be replaced by this, just ensuring that where I have used 10,000 that the number is big enough to cover any data that you might have.

=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0)),0))

To incorporate the extra requirement

=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0))*(vpi!N1:N10000<>"TT"),0))

In relation to the COUNTIF change, try this

=COUNTIFS(vpi!F1:F10000,"Acid",vpi!N1:N10000,"<>TT")

 

[itsgrady]

how about adding an extra condition - heres just 2 of the sumproducts - extend to all the sumproduct
=SUMPRODUCT(--(vpi!F:F="Apple Green"),--(vpi!N:N<>"TT"),vpi!K:K)+SUMPRODUCT(--(vpi!F:F="Redfish"),--(vpi!N:N<>"TT"),vpi!K:K)

On 1 sheet as an example

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																24
3						apple green					1			tt
4
5
6						redfish					20			tt
7						apple green					1
8
9
10						redfish					20			tt
11						apple green					1
12
13
14						redfish					20
15						apple green					1
16
17
18						redfish					20			tt
19						apple green					1
20
Sheet1

Cell Formulas
Range	Formula
P2	P2	=SUMPRODUCT(--(F:F="Apple Green"),--(N:N<>"TT"),K:K)+SUMPRODUCT(--(F:F="Redfish"),--(N:N<>"TT"),K:K)

or without TRUE/FALSE conversion using --
=SUMPRODUCT((F:F="Apple Green")*(N:N<>"TT"),K:K)

Thanks for the advice...

 

[itsgrady]

Firstly, I would recommend not using whole column references, especially for SUMPRODUCT. Your formula requires Excel to check over a million cells and then do over a million multiplications and then add over a million results and then repeat all that 4 times!

Your original formula could be replaced by this, just ensuring that where I have used 10,000 that the number is big enough to cover any data that you might have.

=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0)),0))

To incorporate the extra requirement

=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0))*(vpi!N1:N10000<>"TT"),0))

In relation to the COUNTIF change, try this

=COUNTIFS(vpi!F1:F10000,"Acid",vpi!N1:N10000,"<>TT")

Worked perfect! Thanks for the advice and for taking the time to provide a great solution. This will save me a lot of time. Happy New Year!

 

[Peter_SSs]

Worked perfect! Thanks for the advice and for taking the time to provide a great solution. This will save me a lot of time. Happy New Year!

You're welcome. Glad we could help. Thanks for the follow-up.

 

[etaf]

you are welcome, and Peter_SSs much better solution for later versions of excel - been examining tonight to work out how it works - pretty cool

 

[Peter_SSs]

Actually, some better (more efficient) options still:

Original calculation

=SUM(SUMIFS(vpi!K1:K10000,vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"}))

.. with extra condition

=SUM(SUMIFS(vpi!K1:K10000,vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},vpi!N1:N10000,"<>TT"))