Thread: sumifs with date and text criteria Thanks: 0 Likes: 0

1. sumifs with date and text criteria

Microsoft Excel 2007:

I have the following formula in J45, which gives wrong answer as 0.
=SUMIFS(D5:D33,C5:C33,">="&H40,E5:E33,"<="&H41,G5:G33,"TaxSave")

D5:D33 Amount in numericals
C5:C33 Start date in dd-mm-yyyy format
E5:E33 End date in dd-mm-yyyy format
G5:G33 Text msg, (example: Current Old TaxSave)
H40 Helper with Start date (example 01-04-2018)
H41 Helper with End date (example 31-03-2019)
End date is greater than Start date.

In my worksheet I have text TaxSave in G14 only.

Kindly correct the formula suitably.

Thanking you,

2. Re: sumifs with date and text criteria

The formula looks correct.

What do you get with:

=COUNT(D5:D33)

=COUNT(E5:E33)

=COUNT(E5:E33)

+COUNTIFS(G5:G33,"TextSave")

3. Re: sumifs with date and text criteria

Microsoft Excel 2007

4. Re: sumifs with date and text criteria

Originally Posted by Muthukrishnan V
Microsoft Excel 2007
Sorry, I meant just:

=
COUNTIF(G5:G33,"TextSave")

6. Re: sumifs with date and text criteria

Originally Posted by Muthukrishnan V
Is this count what you would expect to obtain?

7. Re: sumifs with date and text criteria

As requested in my first post, I want to sum from Column D; with start date condition from Column C;
with End date condition from column E; and text condition from Column G.
Kindly refer formula in my first post.
InG14 only text Taxsave. C14 date 30-07-2018. D14 is amount 54718. Correct answer: 54718. But I get 0 answer.

8. Re: sumifs with date and text criteria

Originally Posted by Muthukrishnan V
As requested in my first post, I want to sum from Column D; with start date condition from Column C;
with End date condition from column E; and text condition from Column G.
Kindly refer formula in my first post.
InG14 only text Taxsave. C14 date 30-07-2018. D14 is amount 54718. Correct answer: 54718. But I get 0 answer.
If G14 = TaxSave, C14 = 30-07-2015, D14 = 54718, AND

=G14 = "TaxSave" yields TRUE

=ISNUMBER(C14) yields TRUE

=(C14 >= "30-07-2018"+0) * (C14 <= "30-07-2018"+0) yields 1

=ISNUMBER(D14) yields TRUE

then you should get: 54718 with the formula you already have.

9. Re: sumifs with date and text criteria

Thank you Sir, Great!

10. Re: sumifs with date and text criteria

Originally Posted by Muthukrishnan V
Thank you Sir, Great!
You are welcome.