Multiple conditions with SUM or COUNT

MichaelCreamer

New Member
Joined
Feb 9, 2005
Messages
3
I am trying to using the following formula to calculate the sum of values in column C where 1) The ** appears anywhere in the value of column A and 2) the value of "Red" is in column B.

=SUMPRODUCT((A1:A6="*~*~*")*(B1:B6="Red")*(C1:C6))

table.gif


The wildcard * does not seem to work here. (It does seem to work in COUNTIF and SUMIF but I need to check multiple conditions.)

Is there a way to use wildcard characters here to search for a string anywhere in the value?

I cannot use RIGHT(A1:A7,2), for instance, because the ** (or whatever string) may not always be at the end.

Thanks for any and all help!
Michael
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Try:

=SUMPRODUCT((ISNUMBER(SEARCH("~*~*",A1:A6)))*(B1:B6="Red")*(C1:C6))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Using cell references instead of hard-coding conditions...

=SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(E1,"*","~*"),$A$1:$A$7)),--($B$1:$B$7=E2),$C$1:$C$7)

where E1 houses ** and E2 Red.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,234
Messages
5,623,535
Members
415,976
Latest member
tuananh09x

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
Top