Sumif function with multiple criteria and wildcard

[Learn&Prosper]

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?

Thanks in advance

 

[njimack]

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

 

[T. Valko]

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?

Thanks in advance

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)

 

[Learn&Prosper]

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

 

[Learn&Prosper]

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

 

[Learn&Prosper]

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

 

[T. Valko]

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.