# Sumif function with multiple criteria and wildcard

#### Learn&Prosper

##### New Member
Hi all

The sumif function is basically this --

=sum(if(c2:c1000=c1,if(i2:i000,"*panel*",d2:d1000)))

c2:c1000 contains a date range
cell c1 contains a particular date
i2:i1000 contains text -- some text contains the word "panel" but not by itself hence the "*"
range d2:d1000 contains numbers which i want to sum if the date in range c2:1000 matches c1 and i2:i1000 contains the word "panel"

IT DOESN'T SEEM TO WORK

IT works fine if i use this

=sumif(i2:1000,"*panel*",d2:c1000)

but i need to add the extra criteria of the date.

Can anyone help?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
=SUMPRODUCT(--(C2:C1000=C1),--(ISNUMBER(SEARCH("panel",I2:I1000))),(D2:D1000))

Hi all

The sumif function is basically this --

=sum(if(c2:c1000=c1,if(i2:i000,"*panel*",d2:d1000)))

c2:c1000 contains a date range
cell c1 contains a particular date
i2:i1000 contains text -- some text contains the word "panel" but not by itself hence the "*"
range d2:d1000 contains numbers which i want to sum if the date in range c2:1000 matches c1 and i2:i1000 contains the word "panel"

IT DOESN'T SEEM TO WORK

IT works fine if i use this

=sumif(i2:1000,"*panel*",d2:c1000)

but i need to add the extra criteria of the date.

Can anyone help?

Try one of these.

If you're using Excel 2007 or later:

=SUMIFS(D2:D1000,C2:C1000,C1,I2:I1000,"*panel*")

This one will work in any version of Excel:

=SUMPRODUCT(--(C2:C1000=C1),--(ISNUMBER(SEARCH("panel",I2:I1000))),D2:D1000)

I AM ON EXCEL 2007

I JUST TRIED

=SUMIFS(D2:D1000,C2:C1000,C1,I2:I1000,"*panel*")

AND IT RETURNS VALUE

I AM GOING TRY THE ONE THAT WORKS UNDER ANY VERSION BUT DOES THAT WORK FOR THE WILDCARD? THE RANGE I2:I1000 DOES NOT ONLY CONTAIN THE WORD "PANEL" BUT SOMETIMES OTHER TEXT AS WELL

APPRECIATE THE HELP SO FAR BUT ANY OTHER POINTERS/SUGGESTIONS WELCOME

The one for older versions returns #ref
can't see what i am doing wrong but it doesn't seem to work

I think i just found my mistake
thanks for all the help

I AM ON EXCEL 2007

I JUST TRIED

=SUMIFS(D2:D1000,C2:C1000,C1,I2:I1000,"*panel*")

AND IT RETURNS VALUE

I AM GOING TRY THE ONE THAT WORKS UNDER ANY VERSION BUT DOES THAT WORK FOR THE WILDCARD? THE RANGE I2:I1000 DOES NOT ONLY CONTAIN THE WORD "PANEL" BUT SOMETIMES OTHER TEXT AS WELL

APPRECIATE THE HELP SO FAR BUT ANY OTHER POINTERS/SUGGESTIONS WELCOME
Do you have #VALUE! errors in any of the ranges?

The other formula doesn't support the use of wildcards. The expression: ISNUMBER(SEARCH("panel",I2:I1000)), is what we are using to "mimic" a wildcard.

Replies
3
Views
201
Replies
2
Views
884
Replies
7
Views
475
Replies
16
Views
310
Replies
15
Views
1K

1,203,175
Messages
6,053,936
Members
444,694
Latest member
JacquiDaly

### 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.

### Which adblocker are you using?

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

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