Ok so I have this forumula in a cell:
=SUM((COUNTIF(F7:F69,"<=" & TODAY())),(COUNTIF(B7:B69,">=" & D7)))
and the trouble I am having is that in the second Countif, I need it to check the cell in the "D" column relative to what cell is being checked in the "B" column. Right now it is checking every cell in "B" against "D7" instead of "D8, D9, D10", according to what cell in "B" is selected.
Any help would be appreciated.
Maybe you want?
=SUMPRODUCT((F7:F69<=TODAY())*(B7:B69>=D7:D69))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
that didnt work, just gave me a 0. i tried using D7:D69 in my original formula but it didnt work
Can you explain what exactly you are trying to accomplish, then?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
i want the Countif to check cells B7 thru B69, and count if the relative cell in the "D" column is greater than or equal to the cell in the "B" column
Ok try:
=SUMPRODUCT(--(B7:B69>=D7:D69))
what about the dates in F? Is that a separate count?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
yeah its basically taking the two Countif's and adding them together.
Originally Posted by NBVC
that didn't work either.
Maybe this:
=SUMPRODUCT(--(F7:F69<=TODAY()))+SUMPRODUCT(--(B7:B69>=D7:D69))
Last edited by ptm0412; 07-15-2008 at 01:51 PM.
Oldman Chatting: thanhmy_pham@yahoo.com Mailing: thanhmypham@gmail.com
How didn't it work?
this formula would compare B7 to D7, B8 to D8, B9 to D9, etc and sum up each time the value in B is greater than value in D...
If you have blanks, then you need to adjust formula...
=SUMPRODUCT(--(B7:B69>=D7:D69),--(B7:B69<>""),--(D7:D69<>""))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
ok it worked. thanks a bunch
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks