Calculating a total with 3 different criterias

  • Thread starter Thread starter Desper84AnAnswer
  • Start date Start date
D

Desper84AnAnswer

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)
 
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
 
Try
=Sumproduct(--(B1:B100="AS2"),--(D1:D100="MCT"),--(E1:E100>0))
This will give you the non zero count

If you want the sum then
Sumproduct(--(B1:B100="AS2"),--(D1:D100="MCT"),--(E1:E100>0),(E1:E100))

Adjust the range according to your data.

There are innumerable posts on SUMPRODUCT on this forum.
 
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
 
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
 
Back
Top