how do i set a formulas with two conditions using countif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello

i have a problem with formulas containing two conditions i tried sumif and
countif but it's not working
=COUNT(IF((sheet1!$F$8:$F$300="Materials")*(sheet1!$M$8:$M$300="PPE not
available"),sheet1!$A:$N))

appreciate your help in advnace
thanks
 
To count the number of rows with "Materials" in column F and "PPE not
available" in column M, you could use the DCOUNT function (which requires
setting up a couple of rows for the criteria) or the SUMPRODUCT:
=sumproduct(--(sheet1!$F$8:$F$300="Materials"),--(sheet1!$M$8:$M$300="PPE
not available"))
To add up the associated values from column N where the same criteria are
met, you'd switch to DSUM, or add that array from column N to the sumproduct
formula:
=sumproduct(--(sheet1!$F$8:$F$300="Materials"),--(sheet1!$M$8:$M$300="PPE
not available"),sheet1!$N$8:$N$300)
 
=SUMPRODUCT(--(Sheet1!$F$8:$F$300="Materials"),
--(Sheet1!$M$8:$M$300="PPE not available"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
many thanks for your propmt help it's work

Bob Phillips said:
=SUMPRODUCT(--(Sheet1!$F$8:$F$300="Materials"),
--(Sheet1!$M$8:$M$300="PPE not available"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Back
Top