#1
|
|||
|
|||
Mix COUNTIF and SUMPRODUCT?
I'm trying to count the number of cells starting with "ABC", and the first three numbers are odd. And the cells next to them say "Here". The cells will say something like ABC21710.
I don't even know the what formula needs to be outside, and what formula needs to be inside. I can do a COUNTIFS formula, and a SUMPRODUCT formula to find odd numbers when the whole number is odd, but all of the numbers are technically even.... I'm lost. There's an example spreadsheet attached. Please help Edit for clarification; - Starts with "ABC" - Ends with 5 numbers, all of them are technically ever. I'm looking for the odd numbers, of the first 3 numbers listed. And actually, if there's a way to look at the 6th alphanumeric in, which is all that matters here, then that would work. For example "ABC12310", a formula that would look for the 3, which is the 6th alphanumeric in the example. - Lists "Here in the neighboring cell. |
#2
|
|||
|
|||
I think i got all your criteria on this one
=SUMPRODUCT(--(LEFT(I19:I23,3)="ABC")*(ISODD(MID(I19:I23,4,3)*(J 19:J23="Here")))) |
#3
|
|||
|
|||
Try , Array formula
=COUNT(0/N((LEFT(I19:I23,3)="ABC")*(MOD(ROW(I19:I23),2)=1)* (J19:J23="Here"))) |
#4
|
|||
|
|||
Both work, thanks all.
I changed the first equation a little, in my head it makes more since to change the parenthesis. If this can run into issues, please let me know. Otherwise, problem solved. =SUMPRODUCT(--(LEFT(I18:I28,3)="ABC")*(ISODD(MID(I18:I28,4,3)))* (J18:J28="Here")) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sumproduct | arndts | Excel | 6 | 01-09-2019 01:10 AM |
Issue with SumProduct | Perceptus | Excel | 1 | 03-13-2015 06:23 AM |
Countifs and Sumproduct | Algo | Excel | 6 | 11-13-2012 07:44 AM |
sumproduct?? | jer | Excel | 9 | 10-14-2012 10:00 AM |
Sumproduct | angie.chang | Excel | 3 | 06-14-2012 10:00 AM |