LinkBack URL
About LinkBacks
Hello Friends
Please find the attached file.
I want to count and sum the numbers in the Column B with the dates in Column A for the given month and year.
thanks in advance.
Hello Friends
Please find the attached file.
I want to count and sum the numbers in the Column B with the dates in Column A for the given month and year.
thanks in advance.
Sekar
In H2:
If you want to count instances of datesFormula:
Please Login or Register to view this content.
If you want to count instances of dates but ignore the blanks in column B.Formula:Please Login or Register to view this content.
In I2:
Formula:Please Login or Register to view this content.
Copy down as necessary.
BBS
Last edited by BadlySpelledBuoy; 05-20-2016 at 09:37 AM.
You can use this formula in H2 for the Count:
=SUMPRODUCT(--(TEXT($A$1:$A$92,"mmmyy")=TEXT(G2,"mmmyy")))
and this one in I2 for the Sum:
=SUMPRODUCT(--(TEXT($A$1:$A$92,"mmmyy")=TEXT(G2,"mmmyy")),$B$1:$B$92)
Copy both down as required.
Note that my counts are not the same as yours, though the Sums are.
Hope this helps.
Pete
In H2 Cell
=COUNTIFS(A:A,">="&EOMONTH(G2,-1)+1,A:A,"<="&EOMONTH(G2,0))
In I2 Cell
=SUMIFS(B:B,A:A,">="&EOMONTH(G2,-1)+1,A:A,"<="&EOMONTH(G2,0))
Drag both the formula's down
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
You can use this one in H2:
=SUMPRODUCT((TEXT($A$1:$A$92,"mmmyy")=TEXT(G2,"mmmyy"))*($B$1:$B$92>0))
to get the same results that you have (I wasn't looking at column B earlier).
Hope this helps.
Pete
Hello BBS Pete Seixthsense
All of your formulas working well, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks