SUMPRODUCT Formula & Count IF

itsgrady

Board Regular
Joined
Sep 11, 2022
Messages
132
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
=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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
ABCDEFGHIJKLMNOP
1
224
3apple green1tt
4
5
6redfish20tt
7apple green1
8
9
10redfish20tt
11apple green1
12
13
14redfish20
15apple green1
16
17
18redfish20tt
19apple green1
20
Sheet1
Cell Formulas
RangeFormula
P2P2=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)
 
Upvote 0
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.
Excel Formula:
=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0)),0))

To incorporate the extra requirement
Excel Formula:
=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
Excel Formula:
=COUNTIFS(vpi!F1:F10000,"Acid",vpi!N1:N10000,"<>TT")
 
Upvote 0
Solution
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
ABCDEFGHIJKLMNOP
1
224
3apple green1tt
4
5
6redfish20tt
7apple green1
8
9
10redfish20tt
11apple green1
12
13
14redfish20
15apple green1
16
17
18redfish20tt
19apple green1
20
Sheet1
Cell Formulas
RangeFormula
P2P2=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...
 
Upvote 0
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.
Excel Formula:
=SUM(FILTER(vpi!K1:K10000,ISNUMBER(MATCH(vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},0)),0))

To incorporate the extra requirement
Excel Formula:
=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
Excel Formula:
=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!
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
Actually, some better (more efficient) options still:

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

.. with extra condition
Excel Formula:
=SUM(SUMIFS(vpi!K1:K10000,vpi!F1:F10000,{"Apple Green","Redfish","Dark Blue","Green"},vpi!N1:N10000,"<>TT"))
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top