• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Formula Required

clramjit

New Member
Hi,

I have attached a file with an example of what I'm looking for.
I am trying to get a formula to tally a no. of cells yet omitting a cell count if a certain date condition is met from a referenced cell. It is hard to explain here so a better explanation is contained withing the excel sheet example itself.

Thanks

CLRamjit
 

Attachments

Hui

Excel Ninja
Staff member
E18: =COUNTIFS(E$8:E$16,1,$B$8:$B$16,">0")
E19: =COUNTIFS(E$8:E$16,1,$C$8:$C$16,">0")
E20: =COUNTIFS(E$8:E$16,1,$D$8:$D$16,">0")
copy all across

or

E18: =SUMPRODUCT(($B$8:$D$16>0)*($B$6:$D$6=RIGHT($B18,2))*(E$8:E$16=1))
copy down and cross
 

clramjit

New Member
Hui, thanks for your quick response, however, it's not quite what I'm looking for. Let me explain:

I want E18 to indicate the "1"s that are present on that particular day who are Trained from Col B minus who are "expired" on Col B. So it should be like this {this is the current formula on sheet}
=SUMPRODUCT((E7:E16={"OT",1})*($B$7:$B$16>=DATEVALUE("1/1/2018") [omitting the "1"s that are expired] The bold text is what I don't know how to represent as a formula together with existing formula.
Simple terms I want to represent all that is in current formula subtracting the No. that has expired in Col B for that day(25th).

Hope the explanation was somewhat comprehensible. I've reattached the sheet showing just the related data.

Thank you again for any help and feedback.

CLRamjit
 

Attachments

clramjit

New Member
I think the problem was that the table date was 2017 which meant that all training was current.
Thanks Peter,

Your solution is working, but there is a twist to it now, where I would like to also tally in the same "assigned" range, cells that contain "OT" and in the "Training" range, cells that has "X". With this the hope is to capture some of the other parameters I'm trying to tally, daily. Hope it can be done.

I'v re-attached the sheet with the additional requests.

Thank you very much again for your input.

You all are the Best!

CL Ramjit
 

Attachments

Peter Bartholomew

Well-Known Member
Do you really need to make your data so inconvenient to process? Now both criteria ranges have both numbers and text, each to be tested using an OR condition.

I simplified the test for 1 or "OT" by merely testing for 'not blank', "<>". For the test on the date or "x", I created an array criterion
= IF( {0,1}, ">"&Date, "x" )
and then used SUM to aggregate the resulting counts.

If anyone else has better ideas then, by all means, chip in; I will not be offended!
 

Attachments

clramjit

New Member
Do you really need to make your data so inconvenient to process? Now both criteria ranges have both numbers and text, each to be tested using an OR condition.

I simplified the test for 1 or "OT" by merely testing for 'not blank', "<>". For the test on the date or "x", I created an array criterion
= IF( {0,1}, ">"&Date, "x" )
and then used SUM to aggregate the resulting counts.

If anyone else has better ideas then, by all means, chip in; I will not be offended!
Peter,

Thanks for your help again. Sorry for the lengthy time before replying. I was trying out the solution. It does work on this sheet, however, I tried to use the formula in another sheet and for some reason it seems as though I'm missing something. Reminder I am not versed in excel formulas, I just can express what I want in plain English in the hope that can transform into a formula. Having said that, it is doing what I requested, but how can I use your formula on my real worksheet? Can you expand the hidden names/array please? This way I can try to transform it to the real file.

Thanks Again.

CLRamjit.
 

Attachments

Peter Bartholomew

Well-Known Member
clramjit
Sorry to cause you grief. My basic building blocks are not the Excel defaults that almost everyone else uses. The key is to open up Name Manager on the Formulas ribbon tab.

You will see that 'Training.R1' refers to the absolute range
=TEST!$B$8:$B$16
and, indeed, you could use the direct reference in place of the name.

The personnel assignment is a little more complicated in that 'assigned' is a mixed reference, referring to the column directly above the formula. Note the dollar signs; their placement is important.
=TEST!H$8:H$16

Most obscure of all is 'criterion' that refers to a snippet of a formula
= IF( {0,1}, ">"&Date, "x" )
This evaluates to give a little text array
{"x",">43460"}
which forms the two alternative success criteria. When I use the 'criterion' in the COUNTIF formula, it will evaluate twice, once with the date comparison and the second time simply looking for an "x".

I hope this at least moves you in the right direction to find information elsewhere.
 

clramjit

New Member
Peter, Thanks once again for your patience, I finally figured it out when transferred to my real sheet, at least the part of what you suggested. But in doing so I came across another hiccup. There are other text values that will be placed in the E8:E16 cells, namely "S", "V", "P", which counts certain situations. Is there a way to use exactly what you have given, {= SUM( COUNTIFS( assigned, "<>",Training.R3, criterion) )} and include "S", "V", "P" in the formula as values "not to be counted" if entered? I tried, but couldn't get it. I re-uploaded the sheet to show exactly what I'm talking about.
Peter, I know you're probably fed up with this newbie, but the question is open to anyone in the Forum who can help.

Thank you once again!

CL.
 

Attachments

Peter Bartholomew

Well-Known Member
{= SUM( COUNTIFS( assigned, "<>",Training.R3, criterion) )}
will accept any non-blank in the assigned column. At the other extreme,
{= SUM( COUNTIFS( assigned, "1",Training.R3, criterion) )}
will accept only 1. It will start to get a mess if you wish to treat different characters differently.

COUNTIFS only accepts range references as parameters so if you need to apply a filtering formula first that would require a helper range to select the relevant personnel into the calculation.
 
Top