Issue with adding an actual value to a countif TODAY formula

Hey There

I am trying to create a cell in G2 in which whatever value i put in C2,C3,C4, Etc… only counts if the date i put in B2,B3,B4, Etc… has passed.
Currently this is what I have
=(COUNTIFS(B2:B9,”<="&TODAY(),C2:C9,"”))
However, this seems to only give me a value of 1 whenever i put any value in the C column.
Is there a variation to this formula that will count the exact value I put into C?

I think you are trying to do this:

=COUNTIFS(B2:B9,”<="&TODAY(),C2:C9,"<>”)

As it was, you were only counting the values in C column that were blank when the dates in B column were earlier than (or equal to) today.

Hope this helps.

Pete

That is actually what i have. I guess i copied pasted it wrong in the original thread.
=COUNTIFS(B3:B10,"<="&TODAY(),C3:C10,"<>") - This is what i have in the G2 cell. Anytime i put anything in a cell between C2:C10 (as long as it has passed the current date) than i am getting a value of 1. It does not matter what i put in the C column. Even just . or a word. Is there way to make it count the exact number value i put in the cell?

Do you mean that you want to SUM the values rather than COUNT them?

If so you can try this:

=SUMIF(B2:B9,”<="&TODAY(),C2:C9)

No need for a second condition, as any text values will not be summed.

Hope this helps.

Pete

Jeez Im dumb. I tried Sum but never thought to try SUMIF.

Interestingly, though I took that equation and put it into another cell to generate the same scenario and now it's yielding "0"

=SUMIF(I3:I10,"<="&TODAY(),J3:J10) - I put this into J2 and have a date of 07/31/2017 but I get "0"
However, I do have a large nested =IF equation in there that is generating a number based on what value is in C3:C50.
Would the SUMIF equation you provided have issue with generating a value if the value is being generated because of another cell?
The equation I have in J3 is:
IF(sum(C3:C6)=2.5,"1",if(sum(C3:C6)=3.5,"1.5" + a bunch of other IF variations of the same concept.

Your long IF formula in J3 is returning text values rather than numbers, as you have put quotes around 1 and 1.5 etc. Remove those quotes and you should get your answer.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Again thank you so much
I did not know the "" made it a text value. I assumed I needed the "" the make the IF function work.
It ended up solving the issue.

Originally Posted by yourik

...It ended up solving the issue...

Glad to hear it.

The formula that you have in J3 could probably be simplified and shortened, though you only show a part of it. A VLOOKUP formula could probably be used, or even a straightforward calculation based on the sum of C3:C6. If you post your actual formula in full here, then I can take a look at it.

Hope this helps.

Pete

Sample Point sheet.xlsx

Here is the sheet in its entirety.
Almost All values are locked away behind dates.
The =if equations I have fulfilled a goal to provide as many point variables as I wanted. If there is a formula that would work better im open to suggestion since excel is so massive and versatile. The =Match(sum
The formula works for the printer expectations so I don't have to have an extremely long =If equation for that column.
The Vlookup did not look like it would work for what I was going for unless I understood it incorrectly.
Also, Printer expectations column is based on the Printer sheet
Finally the Attendance, Damage and reshipment Columns are user input which is the only thing that is user input on this sheet.

If there is a way to simplify things I am open to it but ATM it seems to do what I need it to do.

This is the formula that you have in J3:

=IF(SUM(C3:C6)=1,0.5,IF(SUM(C3:C6)=2,1,IF(SUM(C3:C6)=3,1.5,IF(SUM(C3:C6)=4,2,IF(SUM(C3:C6)<=0.9,0,IF(SUM(C3:C6)=0.5,0,IF(SUM(C3:C6)=1.5,0.5,IF(SUM(C3:C6)=2.5,1,IF(SUM(C3:C6)=3.5,1.5)))))))))

and you have a similar formula in K3 and L3, which take the SUM of D3:D6 and E3:E6 respectively. On the next row the formula looks at the next 4 rows, i.e. SUM(C7:C10), and then SUM(C11:C14), and so on.

There are some redundancies in the formula - for example, you check to see if the sum is less than or equal to 0.9 and set the result to 0 if it is, but then you check to see if it is equal to 0.5 and also set it to zero. The conditions and values can be covered by this simple table:

0 .... 0
1 .... 0.5
2 .... 1
3 .... 1.5
4 .... 2

and so you could use VLOOKUP for this - if you put those values in, say, cells S1:T5, then you could have this:

=VLOOKUP(SUM(C3:C6),$S$1:$T$5,2)

or if you didn't want to use any more cells for the table, that would become:

=VLOOKUP(SUM(C3:C6),{0,0;1,0.5;2,1;3,1.5;4,2},2)

This could be copied across into K3 and L3, but you would still have to manually adjust the ranges for the SUM term when you copy it down. To overcome that, you could use this formula in J3:

=VLOOKUP(SUM(INDEX(C:C,(ROWS($1:1)-1)*4+3):INDEX(C:C,ROWS($1:1)*4+2)),{0,0;1,0.5;2,1;3,1.5;4,2},2)

where the term:

SUM(INDEX(C:C,(ROWS($1:1)-1)*4+3):INDEX(C:C,ROWS($1:1)*4+2))

will automatically calculate the correct range depending on the row that it is on - on the second row, for example, that term would become SUM(C7:C10).

So, you can put the formula in J3 and copy across to K3:L3, and then copy those down to row 14.

Hope this helps.

Pete

Hey there Pete,

I never bothered to thank you for the formula you gave me.

the =VLOOKUP(SUM(INDEX(C:C,(ROWS($1:1)-1)*4+3):INDEX(C:C,ROWS($1:1)*4+2)),{0,0;1,0.5;2,1;3,1.5;4,2},2) is just amazing.
I am currently using it in my sheet and made small adjustments to the *4 part by just changing it to *3 and it works fine but the rest baffles me.

This seems like a very complex formula that I try to break up piece by piece to understand but still get stuck. But again thank you for it. Hopefully, i will get to a point where I can understand this and even create detailed formulas like it.