sumifs with date and text criteria

[Muthukrishnan V]

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,

 

[Aladin Akyurek]

The formula looks correct.

What do you get with:

=COUNT(D5:D33)

=COUNT(E5:E33)

=COUNT(E5:E33)

+COUNTIFS(G5:G33,"TextSave")

 

[Muthukrishnan V]

Microsoft Excel 2007
=count(D5:D33) Answer 23
=count(C5:C33) Answer 23
=count(E5:E33) Answer 23
=count(E5:E33)+countif(G5:G33,"TaxSave") Answer 24

 

[Aladin Akyurek]

Microsoft Excel 2007
=count(D5:D33) Answer 23
=count(C5:C33) Answer 23
=count(E5:E33) Answer 23
=count(E5:E33)+countif(G5:G33,"TaxSave") Answer 24

Sorry, I meant just:

=

COUNTIF(G5:G33,"TextSave")

​

 

[Muthukrishnan V]

=COUNTIF(G5:G33,"TaxSave") Answer 1

 

[Aladin Akyurek]

=COUNTIF(G5:G33,"TaxSave") Answer 1

Is this count what you would expect to obtain?

 

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

 

[Aladin Akyurek]

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.

 

[Muthukrishnan V]

Thank you Sir, Great!

 

[Aladin Akyurek]

Thank you Sir, Great!

You are welcome.