Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a total with 3 different criterias
Formula: =COUNTIFS(B:B,"AS2",D:D,"MCT")
In column E (Total amount) - I have dollar values. I want to count all the cells in this column that has a "$" sign. Or... I want the formula to subtract any blank cells in this column so that it gives me a total number. I've tried to use: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"$") - RESULT: "0" (Which is incorrect) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a total with 3 different criterias
You're telling Countifs to count those cells which contain only a $ sign. If
there's anything else in the cell, it won't be counted. You need to use wildcards, as in: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"*$*") Regards, Fred "Desper84AnAnswer" wrote in message ... Formula: =COUNTIFS(B:B,"AS2",D:D,"MCT") In column E (Total amount) - I have dollar values. I want to count all the cells in this column that has a "$" sign. Or... I want the formula to subtract any blank cells in this column so that it gives me a total number. I've tried to use: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"$") - RESULT: "0" (Which is incorrect) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a total with 3 different criterias
Try
=Sumproduct(--(B1:B100="AS2"),--(D1:D100="MCT"),--(E1:E1000)) This will give you the non zero count If you want the sum then Sumproduct(--(B1:B100="AS2"),--(D1:D100="MCT"),--(E1:E1000),(E1:E100)) Adjust the range according to your data. There are innumerable posts on SUMPRODUCT on this forum. "Desper84AnAnswer" wrote: Formula: =COUNTIFS(B:B,"AS2",D:D,"MCT") In column E (Total amount) - I have dollar values. I want to count all the cells in this column that has a "$" sign. Or... I want the formula to subtract any blank cells in this column so that it gives me a total number. I've tried to use: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"$") - RESULT: "0" (Which is incorrect) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a total with 3 different criterias
Hi Fred
I've tried the formula as advised, but its populating an answer of "0". It should be "35". (I manually counted it using the filter option) lol! Is there any other option you can think of? Am I able to send you a copy of the excel sheet that I'm working on? Regards Michaela "Fred Smith" wrote: You're telling Countifs to count those cells which contain only a $ sign. If there's anything else in the cell, it won't be counted. You need to use wildcards, as in: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"*$*") Regards, Fred "Desper84AnAnswer" wrote in message ... Formula: =COUNTIFS(B:B,"AS2",D:D,"MCT") In column E (Total amount) - I have dollar values. I want to count all the cells in this column that has a "$" sign. Or... I want the formula to subtract any blank cells in this column so that it gives me a total number. I've tried to use: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"$") - RESULT: "0" (Which is incorrect) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating a total with 3 different criterias
Are those cellls formatted as currency to show the $, or are they text
values? If the former, then the $ doesn't actually exist within the cell, so you can't search for it directly. Pete On Oct 10, 2:09*am, Desper84AnAnswer wrote: Hi Fred I've tried the formula as advised, but its populating an answer of "0". It should be "35". (I manually counted it using the filter option) lol! Is there any other option you can think of? Am I able to send you a copy of the excel sheet that I'm working on? Regards Michaela "Fred Smith" wrote: You're telling Countifs to count those cells which contain only a $ sign. If there's anything else in the cell, it won't be counted. You need to use wildcards, as in: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"*$*") Regards, Fred "Desper84AnAnswer" wrote in ... Formula: =COUNTIFS(B:B,"AS2",D:D,"MCT") In column E (Total amount) - I have dollar values. *I want to count all the cells in this column that has a "$" sign. *Or... I want the formula to subtract any blank cells in this column so that it gives me a total number. I've tried to use: =COUNTIFS(B:B,"AS2",D:D,"MCT",E:E,"$") - RESULT: "0" (Which is incorrect)- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Total Return | Excel Discussion (Misc queries) | |||
Calculating total time | Excel Worksheet Functions | |||
calculating total time | Excel Discussion (Misc queries) | |||
total of certain cells using 2 criterias | Excel Worksheet Functions | |||
CALCULATING CELLS AS A TOTAL | Excel Discussion (Misc queries) |