Use TEXT function as Criteria on Sumifs to convert month/year to date format

**chico.corrales** · 11-07-2012, 10:48 PM

Hi,

How can i combine a TEXT function (or any function) to work with SumIfs, i have
tried to use ampersand (&) symbol to combine two cells but its not working..

I want to sum all quantity in another sheet based on 2 criteria (product name) and
date (i used data validation) w/ text (Month & Year).

Please help...thanks for looking..

Chico.

**FDibbins** · 11-07-2012, 11:19 PM

see if the attached gives you what you want?

I added a helper column on sheet2 column E, and another 1 on sheet3 for the month number

**Teethless mama** · 11-08-2012, 12:29 AM

This formula doesn't required any helper column(s), or helper worksheet.

In C3: =SUMIFS(DataEntry!C$3:C$63, DataEntry!B$3:B$63, A3,DataEntry!D$3:D$63,">="&--(C$1&D$1),DataEntry!D$3:D$63,"<="&--(C$1&" 31, "&D$1))

**chico.corrales** · 11-08-2012, 05:48 AM

Hi,

Thanks, but i tried this one it returns zero (0) on C3 value.

Thanks.

**chico.corrales** · 11-08-2012, 05:58 PM

Thanks FDibbins,

it worked !!!
Chico

**FDibbins** · 11-08-2012, 06:00 PM

happy to help, and thanks for the rep

**Haseeb Avarakkan** · 11-08-2012, 06:15 PM

You could use EOMONTH in teethless mama's SUMIFS formula,

=SUMIFS(DataEntry!C:C,DataEntry!B:B,A3,DataEntry!D:D,">="&C$1&D$1,DataEntry!D:D,"<="&EOMONTH(C$1&D$1,0))

**chico.corrales** · 11-08-2012, 07:49 PM

Marhaba Haseeb,

It works !!! thanks also..

Use TEXT function as Criteria on Sumifs to convert month/year to date format

LinkBack

Thread Tools

Rate This Thread

Display

Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Re: Use TEXT function as Criteria on Sumifs to convert month/year to date format

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions