Multiply a COUNT IF result

browning.m

Hello! So I have to multiply the count column for when the supplier gets rejected more than one time. Example: In the row that's selected, since the "Date Rejected 02" is not blank, the count column is supposed to equal to "2" and not "1", the same goes for when the date rejected 03 and 04 are not blank. So when "Rejected 02" isn't blank, the result needs to multiply by 2, if "Date rejected 03" isn't blank, the result needs to multiply by 3, and if "Date rejected 04" isn't blank, the result needs to multiply by 4!

The initial formula was:

=IF(COUNTIFS(Supplier:Supplier; Supplier@row) >= 1; COUNTIF(Supplier:Supplier; Supplier@row))

J Tech

Hi @browning.m

To multiply the count column based on the number of rejections, you can modify the formula as follows:

=IF(COUNTIFS(Supplier:Supplier, Supplier@row) >= 1, COUNTIF(Supplier:Supplier, Supplier@row) * (IF(ISBLANK([Date Rejected 02]@row), 1, 2) *  IF(ISBLANK([Date Rejected 03]@row), 1, 3) *  IF(ISBLANK([Date Rejected 04]@row), 1, 4)), 0)

This formula checks if the supplier appears in the Supplier column at least once. If it does, it multiplies the count by the product of 1 (if the corresponding rejection date is blank) and the rejection number (2, 3, or 4). If the supplier does not appear, it returns 0.

Note that this assumes that the rejection dates are in the columns [Date Rejected 02], [Date Rejected 03], and [Date Rejected 04], respectively. Also, this formula does not account for situations where the supplier is rejected multiple times on the same date.

browning.m

@J Tech The formula is working but it's multiplying the results. Ex.: I added a date to the "Date Rejected 03" row, so the count is supposed to be 3, but the result is coming out as 6, and when I added a date to the "Date Rejected 04" row, the result is 24, instead of 4!

browning.m

@Paul Newcome Could you help?

Paul Newcome

I'm not sure I follow what you are trying to do. In your original post, you say that it needs to be multiplied by the number of dates in that 4 column section, but then in your response to @J Tech you indicate that multiplication is wrong.

browning.m

@Paul Newcome Hi! I think maybe it's supposed to SUM, as I said in the last comment, if the "Date Rejected 02" isn't blank, the count is 2; if "Date Rejected 03" isn't blank, the count is supposed to equal to 3; and if "Date Rejected 04" isn't blank, the result is 4!

So I think it could be something like: if the "Date Rejected 02" isn't blank, sum 1+1 and then if "Date Rejected 03" isn't blank either, sum 1+1+1, and the same for the fourth date!

Paul Newcome

In that case, give this a try...

=IF(COUNTIFS(Supplier:Supplier; Supplier@row) >= 1; COUNTIF(Supplier:Supplier; Supplier@row)) + COUNTIFS([Date Rejected 01]@row:[Rejected 04]@row; ISDATE(@cell))

browning.m

@Paul Newcome Hi! I altered this formula to =IF(COUNTIFS(Supplier:Supplier; Supplier@row) >= 1; COUNTIF(Supplier:Supplier; Supplier@row)) + COUNTIFS([Rejected 02]@row:[Rejected 04]@row; ISDATE(@cell)), starting in the second date! Thank you very much!

Paul Newcome

Happy to help. 👍️